C # Operations SQL Server database

<summary>
Create a database
</summary>
<param name= "dbname" ></param>
<param name= "DBPath" ></param>
<returns></returns>
public string Createmssql (String dbname, String dbpath)
{
if (! Serviceisexisted ("MSSQLSERVER"))
{
Return "SQL Server has not been properly configured, please install it in a timely manner. ";
}
if (string. IsNullOrEmpty (dbname))
{
Return "Please enter the database to be created. ";
}
if (string. IsNullOrEmpty (DBPath))
{
Return "Please select the database storage path. ";
}
DataSet ds = new DataSet ();
String consqlserver = "Data source=.;i ntegrated security=true ";
SqlConnection con = new SqlConnection (consqlserver);
Try
{
Con. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. CommandText = "CREATE DATABASE" + dbname + "on primary (name= '" + dbname + "', filename= '" + dbpath + "\ \" + dbname + ". M DF ') Log on (name= ' + dbname + "_log ', filename= '" + dbpath + "\ \" + dbname + "_log.ldf ')";
Cmd. Connection = con;
Cmd. ExecuteNonQuery ();
Return "Database creation succeeded";
}
catch (Exception ex)
{
Return ex. Message;

}
Finally
{
Con. Close ();
Con. Dispose ();
}
}
<summary>
Attaching a database
</summary>
<param name= "dbname" ></param>
<param name= "DBPath" ></param>
<returns></returns>
public string Addmssql (string dbpath)
{
if (string. IsNullOrEmpty (DBPath))
{
Return "no file selected";//"You did not select the database attach path, the attached database failed!" "
}
int i = DBPath. IndexOf ('. ');
int star = DBPath. LastIndexOf (' \ \ ');
String log = DBPath. Substring (0, I);
String dbname = DBPath. Substring (Star + 1, i-star-1);
DataSet ds = new DataSet ();
String consqlserver = "Data source=.;i ntegrated security=true ";
SqlConnection con = new SqlConnection (consqlserver);
Try
{
String STRR = "sp_attach_db" + dbname + "', '" + DBPath + "', '" + log + "_log.ldf";
Con. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = "sp_attach_db" + dbname + "', '" + DBPath + "', '" + log + "_log.ldf";
Cmd. Connection = con;
Cmd. ExecuteNonQuery ();
Return "added Success";
}
catch (Exception ex)
{
Return ex. Message;

}
Finally
{
Con. Close ();
Con. Dispose ();
}
}

<summary>
Gets the database instance name for the specified IP address for all databases.
</summary>
<param name= "IP" > IP address specified. </param>
<param name= "username" > user name of the login database. </param>
<param name= "password" > login database Password. </param>
<returns> returns a list containing the data instance names. </returns>
Private ArrayList joinsqlserver (string IP, string username, string password)
{
ArrayList dbnamelist = new ArrayList ();
SqlConnection Connection = new SqlConnection (
String.Format ("Data source={0};initial Catalog = master; User ID = {1}; PWD = {2} ", IP, username, password));
DataTable dbnametable = new DataTable ();
SqlDataAdapter Adapter = new SqlDataAdapter ("Select name from Master". sysdatabases ", Connection);

Lock (Adapter)
{
Adapter.fill (dbnametable);
}

foreach (DataRow row in dbnametable.rows)
{
Dbnamelist.add (row["name"]);
}

Return dbnamelist;
}
///<summary>
//Read database path
///</summary>
//<param name= "Dataname" ></param& Gt
//<returns></returns>
public string Getsqldatapath (string dataname)
{
String Sqldatapath = String. Empty;
String consqlserver = "Data source=.;i ntegrated security=true ";
SqlConnection connetion = new SqlConnection (consqlserver);
Try
{
//String strSQL = "Select filename from" + Dataname + ": Sysfiles ";
String strSQL = string. Format ("Select filename from {0}".. Sysfiles ", dataname);
SqlCommand command = new SqlCommand (strSQL);
Command. Connection = connetion;
Connetion. Open ();
using (SqlDataReader reader = command. ExecuteReader ())
{
while (reader. Read ())
{
Sqldatapath = reader[0]. ToString ();
int end = Sqldatapath. LastIndexOf (' \ \ ');
Sqldatapath = Sqldatapath. Substring (0, end);

}
Reader. Close ();
}
}
Catch {if (string. IsNullOrEmpty (Sqldatapath)) return string. Empty; }
Finally
{
Connetion. Close ();
}
return sqldatapath;
}
<summary>
Get the SQL Server installation path
</summary>
<returns></returns>
public string Getsqlserverpath ()
{
String connctionstring = "Data source=.;i ntegrated security=true ";
SqlConnection connetion = new SqlConnection (connctionstring); Connect to SQL
String SQLPath = String. Empty;
Official version
Try
{//Get SQL Server installation path
String strSQL = "EXEC master". Xp_regread ' HKEY_LOCAL_MACHINE ', ' Software\\Microsoft\\Microsoft SQL server\\mssql10. Mssqlserver\\setup ', ' sqldataroot ' ";

SqlCommand command = new SqlCommand (strSQL);
Command. Connection = connetion;
Connetion. Open ();
using (SqlDataReader reader = command. ExecuteReader ())
{
while (reader. Read ())
{
SQLPath = reader[1]. ToString ();
}
Reader. Close ();
}
}
Catch {if (string. IsNullOrEmpty (SQLPath)) return string. Empty; }
Finally
{
Connetion. Close ();
}
if (string. IsNullOrEmpty (SQLPath))
{
Beta quick Release
Try
{//Get SQL Server installation path
String strSQL = "EXEC master". Xp_regread ' HKEY_LOCAL_MACHINE ', ' Software\\Microsoft\\Microsoft SQL server\\mssql10. Sqlexpress\\setup ', ' sqldataroot ' ";
SqlCommand command = new SqlCommand (strSQL);
Command. Connection = connetion;
Connetion. Open ();
using (SqlDataReader reader = command. ExecuteReader ())
{
while (reader. Read ())
{
SQLPath = reader[1]. ToString ();
}
Reader. Close ();
}
}
Catch {if (string. IsNullOrEmpty (SQLPath)) return string. Empty; }
Finally
{
Connetion. Close ();
}
}

return SQLPath;
}
<summary>
Restoring a Database
</summary>
<param name= "Backfile" ></param>
<returns></returns>
public string Restoredatabase (String dbname, String backfile)//Database name Backup path
{
if (!string. IsNullOrEmpty (Backfile))
{
String retu = "";
"RESTORE Database" + dbname + "from disk= ' c:\\ your full backup filename ' with NORECOVERY '
Kill all the original database connection processes
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Data source=.;i ntegrated security=true ";
Conn. Open ();
String sql = "Select spid from Master." sysprocesses, sysdatabases where Sysprocesses.dbid=sysdatabases.dbid and sysdatabases. Name= ' "+ dbname +" ' ";
SqlCommand cmd1 = new SqlCommand (SQL, conn);
SqlDataReader Dr;
ArrayList list = new ArrayList ();
Try
{
Dr = Cmd1. ExecuteReader ();
while (Dr. Read ())
{
List. ADD (Dr. GetInt16 (0));
}
Dr. Close ();
}
catch (Exception e)
{
Retu = E.tostring ();
}
Finally
{
Conn. Close ();
}
for (int i = 0; i < list. Count; i++)
{
Conn. Open ();
CMD1 = new SqlCommand (string. Format ("KILL {0}", List[i]. ToString ()), conn);
Cmd1. ExecuteNonQuery ();
Conn. Close ();
}

string constr = @ "Data source=.;i ntegrated security=true ";
string database = dbname;
string path = Backfile;

String BACKUP = String.Format ("RESTORE database {0} from DISK = ' {1} ' with replace ', DATABASE, path);//with Norecovery
SqlConnection con = new SqlConnection (CONSTR);
SqlCommand cmd = new SqlCommand (BACKUP, con);
Con. Open ();
Try
{
Cmd. ExecuteNonQuery ();
Retu = "Restore succeeded";
Application.exit ();//Close Program
}
catch (Exception E)//SqlException ee)
{
Retu = E.tostring ();
}
Finally
{
Con. Close ();
}
Return retu;//"string of success or not";
}
Else
{
Return "no file selected";//You did not select the database file to restore, the restore failed!
}

}

<summary>
Deleting a database
</summary>
<param name= "dbname" ></param>
<returns></returns>
public string Delmssql (String dbname)
{
DataSet ds = new DataSet ();
String consqlserver = "Data source=.;i ntegrated security=true ";
SqlConnection con = new SqlConnection (consqlserver);
Try
{
if (MessageBox.Show ("OK" to delete the database? "," Confirm ", Messageboxbuttons.yesno, System.Windows.Forms.MessageBoxIcon.Warning) = = Dialogresult.yes)
{
Con. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = @ "DROP database" + dbname;
Cmd. Connection = con;
Cmd. ExecuteNonQuery ();
Return "Database has been deleted";
}
return null;
}
catch (Exception ex)
{
Return ex. Message;

}
Finally
{
Con. Close ();
Con. Dispose ();
}
}

<summary>
SQL Server backs up the database to a local
</summary>
<param name= "db" > Database </param>
public bool Backsqltolocal (string db,string path)
{
if (!string. IsNullOrEmpty (PATH))
{
String sql = "";
String backname = db + "_" + DateTime.Now.ToString ("Yyyymmddhhmmss");
if (!string. IsNullOrEmpty (PATH))
{
String filepath = path + "\ \" + Backname + ". Bak";
if (file.exists (db))//Determine if the file exists
//{
Delete if it exists
//}
sql = string. Format ("use Master;backup database {0} to disk = ' {1} ';", DB, FilePath);
}
Else
{
sql = string. Format ("use Master;backup database {0} to disk = ' {1} ';", DB, Backname + ". bak");
}
Conn. Open ();
SqlCommand comm = new SqlCommand (SQL, conn);
Comm.commandtype = CommandType.Text;
Try
{
Comm. ExecuteNonQuery ();
}
catch (Exception err)
{
String str = Err. Message;
Conn. Close ();
return false;
}
Conn. Close ();//Closing database connection
return true;
}
Else
{
return false;
}
}

C # Operations SQL Server database