C # Call and execute the SQL script file

Connection: http://www.cnblogs.com/long-gengyun/archive/2012/05/25/2517954.html

The task of the leader is suddenly received, and a small tool is written for O & M. A parameter is assigned, an SQL script file is called, a parameter is replaced, and then executed.

So the job started...

First, let C #ProgramDirectly execute the SQL script file. Similar to SSMs, you can directly open the SQL file and execute the file. The search result is true. However, you need to borrow the following class libraries and namespaces of MSSQL:

Microsoft. sqlserver. connectioninfo. dll

Microsoft. sqlserver. SMO. dll

--------------------------------------------

Using Microsoft. sqlserver. Management. Common;

Using Microsoft. sqlserver. Management. SMO;

Here is a small plug-in. For vs2008, you can directly reference these two class libraries, but the reference in vs2010 cannot find these two class libraries (I don't know how Microsoft considers them)

Fortunately, my computer has been installed in both vs2008 and vs2010.

However, it is difficult for me to avoid it. vs2010 cannot find these two class libraries in reference, so we can find a way to find them. First, mssqlserver2008 is installed on my computer,

Go to X: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ assemblies to find

Microsoft. sqlserver. connectioninfo. dll

Microsoft. sqlserver. SMO. dll

Microsoft. sqlserver. Management. SDK. SFC. dll)

Then manually add the application. The third dll must be referenced. Otherwise, an error will be reported.

See belowCode:

 
// Card filling operation
 

 
Private VoidPatchcard ()
 

 
{
 

 
StringPath = system. environment. currentdirectory;
 

 
StringCardno = txtcardno. Text. Trim ();
 

 
StringConnectonstring = configurationmanager. etettings ["Connectionstring"]. Tostring ();
 

If(Cardno =Null| Cardno ="")
 

 
{
 

 
MessageBox. Show ("The card number cannot be blank! ");
 

 
Return;
 

 
}
 

 
If(! Path. endswith (@"\"))
 

 
{
 

 
Path + = @"\";
 

 
}
 

 
Path + = "Blue Whale card. SQL"; // Obtain the script location
 

 
If (file. exists (PATH ))
 

 
{
 

Fileinfo file = new fileinfo (PATH );
 

 
String script = file. opentext (). readtoend ();
 

 
Script = script. Replace ("h00001", Cardno); // Replace the parameters in the script
 

 
Try
 

 
{
 

 
// Execute the script
 

 
Sqlconnection conn = new sqlconnection (connectonstring );
 

 
Microsoft. sqlserver. Management. SMO. Server = new server (New serverconnection (conn ));
 

 
Int I = server. connectioncontext. executenonquery (SCRIPT );
 

 
If (I = 1)
 

 
{
 

MessageBox. Show ("Congratulations! \ N"+ Cardno +"Card makeup successful!","Successful");
 

 
Txtcardno. Text ="";
 

 
Createlog (cardno, true );
 

 
}
 

 
Else
 

 
{
 

 
MessageBox. Show ("@ try again!","Failed");
 

 
}
 

 
}
 

 
Catch (exception es)
 

 
{
 

 
MessageBox. Show (ES. Message );
 

Createlog (cardno +""+ ES. Message, false );
 

 
}
 

 
}
 

 
Else
 

 
{
 

 
MessageBox. Show ("the script does not exist! ");
 

 
Return;
 

 
}
 

 
}
 

OK. It's easy to see ~~

Hard work and self-owned gains