C#操作Sql server資料庫

標籤：

/// <summary>
/// 建立資料庫
/// </summary>
/// <param name="dbname"></param>
/// <param name="dbpath"></param>
/// <returns></returns>
public string CreateMSSQL(string dbname, string dbpath)
{
if (!ServiceIsExisted("MSSQLSERVER"))
{
return "SQL Server 尚未正確配置，請及時進行安裝。";
}
if (string.IsNullOrEmpty(dbname))
{
return "請輸入要建立的資料庫。";
}
if (string.IsNullOrEmpty(dbpath))
{
return "請選擇資料庫存放路徑。";
}
DataSet ds = new DataSet();
string consqlserver = "Data Source=.;Integrated 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 + ".mdf‘) log on (name=‘" + dbname + "_log‘,filename=‘" + dbpath + "\\" + dbname + "_log.ldf‘)";
cmd.Connection = con;
cmd.ExecuteNonQuery();
return "資料庫建立成功";
}
catch (Exception ex)
{
return ex.Message;

}
finally
{
con.Close();
con.Dispose();
}
}
/// <summary>
/// 附加資料庫
/// </summary>
/// <param name="dbname"></param>
/// <param name="dbpath"></param>
/// <returns></returns>
public string ADDMSSQL(string dbpath)
{
if (string.IsNullOrEmpty(dbpath))
{
return "未選擇檔案";//"您未選擇資料庫附加路徑，附加資料庫失敗！"
}
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=.;Integrated 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 "附加成功";
}
catch (Exception ex)
{
return ex.Message;

}
finally
{
con.Close();
con.Dispose();
}
}

/// <summary>
/// 擷取指定IP地址的資料庫所有資料庫執行個體名。
/// </summary>
/// <param name="ip">指定的 IP 位址。</param>
/// <param name="username">登入資料庫的使用者名稱。</param>
/// <param name="password">登陸資料庫的密碼。</param>
/// <returns>返回包含資料執行個體名的列表。</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>
/// 讀取資料庫路徑
/// </summary>
/// <param name="dataname"></param>
/// <returns></returns>
public string getSqlDataPath(string dataname)
{
string sqldatapath = string.Empty;
string consqlserver = "Data Source=.;Integrated 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>
/// 擷取SQL server 安裝路徑
/// </summary>
/// <returns></returns>
public string getSqlServerPath()
{
string connctionString = "Data Source=.;Integrated Security=True";
SqlConnection connetion = new SqlConnection(connctionString); // 串連sql
string sqlpath = string.Empty;
//正式版
try
{ // 擷取sql server安裝路徑
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))
{
//測試版 快捷版
try
{ // 擷取sql server安裝路徑
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>
/// 還原資料庫
/// </summary>
/// <param name="backfile"></param>
/// <returns></returns>
public string RestoreDatabase(string dbname, string backfile)//資料庫名稱 備份路徑
{
if (!string.IsNullOrEmpty(backfile))
{
string retu = "";
//"restore database " + dbname + " from disk=‘c:\\你的完全備份檔案名‘ with norecovery"
///殺死原來所有的資料庫連接進程
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.;Integrated 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=.;Integrated 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 = "還原成功";
//Application.Exit();//關閉程式
}
catch (Exception e)// SqlException ee)
{
retu = e.ToString();
}
finally
{
con.Close();
}
return retu;//"成功與否字串";
}
else
{
return "沒選擇檔案";//您沒有選擇要還原的資料庫檔案,還原失敗！
}

}

 

/// <summary>
/// 刪除資料庫
/// </summary>
/// <param name="dbname"></param>
/// <returns></returns>
public string delMSSQL(string dbname)
{
DataSet ds = new DataSet();
string consqlserver = "Data Source=.;Integrated Security=True";
SqlConnection con = new SqlConnection(consqlserver);
try
{
if (MessageBox.Show("確定要刪除該資料庫？", "確認", 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 "資料庫已經刪除";
}
return null;
}
catch (Exception ex)
{
return ex.Message;

}
finally
{
con.Close();
con.Dispose();
}
}

 

/// <summary>
/// sql server 備份資料庫到本地
/// </summary>
/// <param name="db">資料庫</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))//判斷檔案是否存在
//{
// //如果存在則刪除
//}
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();//關閉資料庫連接
return true;
}
else
{
return false;
}
}

C#操作Sql server資料庫