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;
}
}