SQLServer通用类A
using System; using System.Data; using System.Data.SqlClient; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms; using System.Xml; namespace CommonLib { class DB_SQLServerA { /// <summary> /// 数据库连接字符串 /// </summary> //public static string connectionString = String.Format("packet size=4096;data source={0};persist security info=True;initial catalog={1};user id={2};password={3}", // ConfigurationManager.AppSettings["DataSourse"], ConfigurationManager.AppSettings["DataBase"], ConfigurationManager.AppSettings["UserName"], ConfigurationManager.AppSettings["PassWord"]); ////public static readonly string connectionString = String.Format("packet size=4096;data source={0};persist security info=True;initial catalog={1};user id={2};password={3}", System.Configuration.ConfigurationManager.AppSettings["ServerIP"], // System.Configuration.ConfigurationManager.AppSettings["Server"], System.Configuration.ConfigurationManager.AppSettings["user"], System.Configuration.ConfigurationManager.AppSettings["password"]); // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); public static string connectionString = ""; public static void SetLinkString(string strDataource, string strDataBase, string strUserID, string strPWD) { if ((strDataBase == null) || (strDataBase.Length <= 0) || (strDataource == null) || (strDataource.Length <= 0) || (strUserID == null) || (strUserID.Length <= 0) || (strPWD == null) || (strPWD.Length <= 0)) { MessageBox.Show("输入链接字符串属性错误"); } else { connectionString = "packet size=4096;data source=" + strDataource + ";persist security info=True;initial catalog=" + strDataBase + ";user id=" + strUserID + ";password=" + strPWD + ";"; } } #region//GetTable方法 /// <summary> /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接 /// 使用参数数组提供参数 /// </summary> /// <param name="connecttionString">一个现有的数据库连接</param> /// <param name="cmdTye">SqlCommand命令类型</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection QuaryData(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connecttionString)) { PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(ds); } DataTableCollection table = ds.Tables; return table; } /// <summary> /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接 /// 使用参数数组提供参数 /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <returns></returns> public static DataTableCollection QuaryData(string cmdText) { return QuaryData(DB_SQLServerA.connectionString, CommandType.Text, cmdText, null); } /// <summary> /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接 /// 使用参数数组提供参数 /// </summary> /// <param name="cmdTye">SqlCommand命令类型</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection QuaryData(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { return QuaryData(DB_SQLServerA.connectionString, cmdTye, cmdText, commandParameters); } /// <summary> /// 存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection QuaryDataProducts(string cmdText, SqlParameter[] commandParameters) { return QuaryData(DB_SQLServerA.connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// Sql语句专用 /// </summary> /// <param name="cmdText"> T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection QuaryDataText(string cmdText, SqlParameter[] commandParameters) { return QuaryData(DB_SQLServerA.connectionString, CommandType.Text, cmdText, commandParameters); } #endregion /// <summary> /// 为执行命令准备参数 /// </summary> /// <param name="cmd">SqlCommand 命令</param> /// <param name="conn">已经存在的数据库连接</param> /// <param name="trans">数据库事物处理</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param> /// <param name="cmdParms">返回带参数的命令</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { //判断数据库连接状态 if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; //判断是否需要事物处理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #region//ExecteNonQuery方法 /// <summary> ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。 /// 使用参数数组形式提供参数列表 /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); //清空SqlCommand中的参数列表 cmd.Parameters.Clear(); return val; } } /// <summary> ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。 /// 使用参数数组形式提供参数列表 /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters); } /// <summary> ///存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQueryProducts(string cmdText, params SqlParameter[] commandParameters) { return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> ///Sql语句专用 /// </summary> /// <param name="cmdText">T_Sql语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQueryText(string cmdText, params SqlParameter[] commandParameters) { return ExecteNonQuery(CommandType.Text, cmdText, commandParameters); } /// <summary> ///Sql语句专用 /// </summary> /// <param name="cmdText">T_Sql语句</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQueryText(string cmdText) { return ExecteNonQuery(CommandType.Text, cmdText, null); } #endregion #region //ExecuteReader方法 /// <summary> /// 对数据库执行一个SqlCommand,返回一个结果集在连接字符串中指定使用所提供的参数。 /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); rdr.Close(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// 对数据库执行一个SqlCommand,返回一个结果集在连接字符串中指定使用所提供的参数。 /// </summary> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string cmdText) { return ExecuteReader(connectionString, CommandType.Text, cmdText, null); } /// <summary> /// 对数据库执行一个SqlCommand,返回一个结果集在连接字符串中指定使用所提供的参数。 /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecuteReader(connectionString, cmdType, cmdText, null); } /// <summary> /// 对数据库执行一个SqlCommand,返回一个结果集在连接字符串中指定使用所提供的参数。 /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReaderSql(string cmdText, params SqlParameter[] commandParameters) { return ExecuteReader(connectionString, CommandType.Text, cmdText, commandParameters); } /// <summary> /// 对数据库执行一个SqlCommand,返回一个结果集在连接字符串中指定使用所提供的参数。 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReaderProducts(string cmdText, params SqlParameter[] commandParameters) { return ExecuteReader(connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } #endregion #region//ExecuteDataSet方法 /// <summary> /// return a dataset /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); return ds; } catch { conn.Close(); throw; //return null; } } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <returns></returns> public static DataSet ExecuteDataSet(string cmdText) { return ExecuteDataSet(connectionString, CommandType.Text, cmdText, null); } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters); } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSetProducts(string cmdText, params SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSetText(string cmdText, params SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters); } /// <summary> /// 返回视图 /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="sortExpression">排序列</param> /// <param name="direction">方向</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个视图</returns> public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); DataView dv = ds.Tables[0].DefaultView; dv.Sort = sortExpression + " " + direction; return dv; } catch { conn.Close(); throw; } } #endregion #region // ExecuteScalar方法 /// <summary> /// 返回第一行的第一列 /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <returns>返回一个对象</returns> public static object ExecuteScalar(string cmdText) { return ExecuteScalar(DB_SQLServerA.connectionString, CommandType.Text, cmdText, null); } /// <summary> /// 返回第一行的第一列 /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { return ExecuteScalar(DB_SQLServerA.connectionString, cmdType, cmdText, commandParameters); } /// <summary> /// 返回第一行的第一列存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalarProducts(string cmdText, params SqlParameter[] commandParameters) { return ExecuteScalar(DB_SQLServerA.connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// 返回第一行的第一列Sql语句专用 /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalarText(string cmdText, params SqlParameter[] commandParameters) { return ExecuteScalar(DB_SQLServerA.connectionString, CommandType.Text, cmdText, commandParameters); } /// <summary> /// 执行一个SqlCommand返回第一列的第一个记录对数据库连接字符串中指定使用所提供的参数。 /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection connection = new SqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> ///执行一个SqlCommand返回第一列的第一个记录对一个现有的数据库连接使用所提供的参数。 /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns> public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } #endregion /// <summary> ///参数数组添加到缓存中 /// </summary> /// <param name="cacheKey">关键参数缓存</param> /// <param name="cmdParms">要缓存SqlParamters数组</param> public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// 检索缓存参数 /// </summary> /// <param name="cacheKey">主要用于查询参数</param> /// <returns>缓存SqlParamters数组</returns> public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// <summary> /// 检查是否存在 /// </summary> /// <param name="strSql">Sql语句</param> /// <returns>bool结果</returns> public static bool Exists(string strSql) { int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, null)); if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 检查是否存在 /// </summary> /// <param name="strSql">Sql语句</param> /// <param name="cmdParms">参数</param> /// <returns>bool结果</returns> public static bool Exists(string strSql, params SqlParameter[] cmdParms) { int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms)); if (cmdresult == 0) { return false; } else { return true; } } private static string ServerName; private static string UserName; private static string Password; private static string DataBase; public static void ConnectDb() { XmlDocument doc = new XmlDocument(); //获得配置文件的全路径 string strFileName = AppDomain.CurrentDomain.SetupInformation.ConfigurationFile; doc.Load(strFileName); XmlNodeList nodes = doc.GetElementsByTagName("add"); for (int i = 0; i < nodes.Count; i++) { //获得将当前元素的key属性 XmlAttribute att = nodes[i].Attributes["key"]; //根据元素的第一个属性来判断当前的元素是不是目标元素 if (att.Value == "ServerIP") { //对目标元素中的第二个属性赋值 att = nodes[i].Attributes["value"]; ServerName = att.Value; continue; } else if (att.Value == "Server") { //对目标元素中的第二个属性赋值 att = nodes[i].Attributes["value"]; DataBase = att.Value; continue; } else if (att.Value == "user") { //对目标元素中的第二个属性赋值 att = nodes[i].Attributes["value"]; UserName = att.Value; continue; } else if (att.Value == "password") { //对目标元素中的第二个属性赋值 att = nodes[i].Attributes["value"]; Password = att.Value; continue; } connectionString = string.Format("packet size=4096;data source={0};persist security info=True;initial catalog={1};user id={2};password={3}", ServerName, DataBase, UserName, Password); } } } }
SQL server通用类B
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; using System.Threading; namespace CommonLib { public class DB_SQLServerB { public static string BuildConnString(string strDataSource,string strDataBase,string strUserID,string strPsaaword) { return "packet size=4096;Data Source=" + strDataSource + ";Initial Catalog=" + strDataBase + ";Persist Security Info=True;User ID=" + strUserID + ";Password=" + strPsaaword + ";"; } public static DataSet Adapter(string connstr, CommandType cmdType, string cmdText) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = cmdType; cmd.CommandText = cmdText; cmd.Connection.Open(); SqlDataAdapter ada = new SqlDataAdapter(cmd); cmd.CommandTimeout = 0; ada.Fill(ds); cmd.Connection.Close(); cmd.Dispose(); return ds; } public static DataSet Adapter(string connstr, string cmdText) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = cmdText; cmd.Connection.Open(); SqlDataAdapter ada = new SqlDataAdapter(cmd); cmd.CommandTimeout = 0; ada.Fill(ds); cmd.Connection.Close(); cmd.Dispose(); return ds; } /// <summary> /// 执行无返回结果集操作,返回执行条数 /// </summary> /// <param name="connstr">数据库连接串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <returns>执行条数</returns> public static int ExecuteNonQuery(string connstr, CommandType cmdType, string cmdText) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = cmdType; cmd.CommandText = cmdText; cmd.Connection.Open(); int val = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return val; } /// <summary> /// 执行无返回结果集操作,返回执行条数 /// </summary> /// <param name="connstr">数据库连接串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <param name="myParameter">参数</param> /// <returns>执行条数</returns> public static int ExecuteNonQueryProcedureParm(string connstr, CommandType cmdType, string cmdText,SqlParameter myParameter) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = cmdType; cmd.CommandText = cmdText; cmd.Parameters.Add(myParameter); cmd.Connection.Open(); int val = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return val; } public static int ExecuteNonQuery(string connstr, string cmdText) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = cmdText; cmd.Connection.Open(); int val = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return val; } /// <suPASSWORDary> /// 执行无返回结果集操作 /// </suPASSWORDary> /// <param name="connString">数据库连接串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static int ExecuteNonQuery(string connString, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connString)) { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <suPASSWORDary> /// 通过事务控制 ，执行一组无返回结果集操作 /// </suPASSWORDary> /// <param name="connString">数据库连接串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static int ExecuteNonQuery(string connString, List<string> listCmdText, List<SqlParameter[]> listCmdParms) { if (listCmdText.Count != listCmdParms.Count) { return -1; } int val = 0; SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } SqlTransaction trans = conn.BeginTransaction(); try { for (int i = 0; i < listCmdText.Count; i++) { PrepareCommand(cmd, conn, trans, CommandType.Text, listCmdText[i], listCmdParms[i]); val += cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch(Exception ex) { trans.Rollback(); if (conn.State == ConnectionState.Open) { conn.Close(); } throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } } return val; } /// </suPASSWORDary> /// <param name="connString">数据库连接</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <param name="cmdParms">参数</param> /// <returns>数据集</returns> public static DataSet Adapter(string connString, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connString)) { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms); SqlDataAdapter ada = new SqlDataAdapter(cmd); ada.Fill(ds); cmd.Parameters.Clear(); return ds; } } /// <suPASSWORDary> /// 准备执行 /// </suPASSWORDary> /// <param name="cmd">命令对象</param> /// <param name="conn">数据库连接对象</param> /// <param name="trans">事务对象</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令内容</param> /// <param name="cmdParms">参数</param> private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = cmdType; if (trans != null) cmd.Transaction = trans; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 测试数据库链接 /// </summary> /// <param name="connstr">数据库连接串</param> public static bool TestConnection(string connstr) { bool IsCanConnectioned = false; SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connstr); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = string.Empty; try { cmd.Connection.Open(); cmd.Dispose(); IsCanConnectioned = true; } catch (Exception ex) { //throw ex; IsCanConnectioned = false; } finally { conn.Close(); } return IsCanConnectioned; } public static bool BackupDataBase(string strSourceDB,string strPathFile, string strConnSourceDB, string strConnBackupDB) { bool IsBackUpSuccess = false; string time = Convert.ToDateTime(DB_SQLServerB.Adapter(strConnBackupDB, "select getdate()").Tables[0].Rows[0][0].ToString()).ToString("yyMMdd"); //服务器当前时间130301 DataTable dt = DB_SQLServerB.Adapter(strConnBackupDB, "select top 1 * from CSF_BakFileList order by CreateTime desc ").Tables[0]; int number; string no = "0000"; if (dt.Rows.Count <= 0) { no = "0001"; } else { string BID = dt.Rows[0]["FileName"].ToString(); //备份1406190005.bak string filetime = BID.Substring(2, 6); no = BID.Substring(8, 4); if (filetime == time)//服务器时间和备份文件时间是一天 { number = Convert.ToInt32(no); if (number < 9) { no = "000" + (number + 1).ToString(); } else if (number >= 9 && number <= 98) { no = "00" + (number + 1).ToString(); } else if (number > 98 && number <= 998) { no = "0" + (number + 1).ToString(); } else if (number > 998 && number <= 9998) { no = (number + 1).ToString(); } } else { no = "0001"; } } string filepath = "备份" + time + no + ".bak"; //string strbackup = "backup database " + strSourceDB + " to disk='" + strPathFile + "\\" + filepath + "' with init"; string strbackup = @"declare @path nvarchar(1000) set @path='" + strPathFile + @"' declare @temp table(a int,b int ,c int) insert @temp exec master..xp_fileexist @path if not exists(select * from @temp where b = 1) ExEc xp_cmdshell 'mkdir " + strPathFile + "';" + "backup database " + strSourceDB + " to disk='" + strPathFile + "\\" + filepath + "' with init"; SqlParameter[] parameters = { new SqlParameter("@FileNm", SqlDbType.NVarChar,50) }; parameters[0].Value = filepath; string strinsert = "insert into CSF_BakFileList (FileName )values(@FileNm)"; try { int result = DB_SQLServerB.ExecuteNonQuery(strConnSourceDB, strbackup); int resultIS = DB_SQLServerB.ExecuteNonQuery(strConnBackupDB, strinsert, parameters); IsBackUpSuccess = true; Helper_ShowMSG.ShowMessageInformation("备份成功！"); } catch (Exception ex) { if (ex.Message.IndexOf("系统找不到指定的路径") != -1) { Helper_ShowMSG.ShowMessageError("未找到备份文件路径！请查看路径" + strPathFile + " 是否存在!"); IsBackUpSuccess = false; } else { Helper_ShowMSG.ShowMessageError("备份文件失败！错误原因，" + ex.Message + " " + strbackup); IsBackUpSuccess = false; } } finally { } return IsBackUpSuccess; } public static bool RestoreDataBase(string strPath,string strBackFile,string connMaster,string connBackDB) { bool bHYFlag = false; DataTable spidTable = DB_SQLServerB.Adapter(connMaster, "select spid from master..sysprocesses where dbid=db_id('CSF_UserAuth')").Tables[0]; for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++) { string strShutUp = "kill " + spidTable.Rows[iRow][0].ToString(); //强行关闭用户进程 int result = DB_SQLServerB.ExecuteNonQuery(connMaster, strShutUp); } string strSqlFirst = " ALTER DATABASE [CSF_UserAuth] SET OFFLINE WITH ROLLBACK IMMEDIATE "; string strSqlLast = "ALTER DATABASE [CSF_UserAuth] SET ONLINE WITH ROLLBACK IMMEDIATE"; string strSqlRT = @"restore database CSF_UserAuth from disk='" + strPath + @"\" + strBackFile + "' " +"with move 'CSF_UserAuth' to 'c:\\CSF_UserAuth.mdf'" + ",move 'CSF_UserAuth_log' to 'c:\\CSF_UserAuth.ldf'" + ", REPLACE "; MessageBox.Show(strSqlRT); try { int resultFirst = DB_SQLServerB.ExecuteNonQuery(connMaster, strSqlFirst); MessageBox.Show("还原步骤一成功！"); int resultRT = DB_SQLServerB.ExecuteNonQuery(connMaster, strSqlRT); MessageBox.Show("还原步骤二成功！"); int resultLast = DB_SQLServerB.ExecuteNonQuery(connMaster, strSqlLast); MessageBox.Show("还原步骤三成功！"); SqlConnection.ClearAllPools(); MessageBox.Show("还原步骤四成功！"); bHYFlag = true; } catch (Exception ex) { bHYFlag = false; int resultLast = DB_SQLServerB.ExecuteNonQuery(connMaster, strSqlLast); Thread.Sleep(500); if (ex.Message.IndexOf("系统找不到指定的文件") != -1) { DialogResult dr = Helper_ShowMSG.ShowMessageQuestion("该备份文件可能已被删除！请先删除数据库中有关该文件的记录！确定要删除该记录吗？"); if (dr == DialogResult.Yes) { string strSql = @"delete from CSF_BakFileList where FileName ='" + strBackFile + "'"; int re = DB_SQLServerB.ExecuteNonQuery(connBackDB, strSql); Thread.Sleep(500); } } else if (ex.Message.IndexOf("系统找不到指定的路径") != -1) { DialogResult dr = MessageBox.Show("未找到备份文件！请检查文件" + strBackFile + " 是否不存在!"); if (dr == DialogResult.OK) { string strSql = @"delete from CSF_BakFileList where FileName ='" + strBackFile + "'"; int re = DB_SQLServerB.ExecuteNonQuery(connBackDB, strSql); Thread.Sleep(500); } } else { MessageBox.Show("还原失败！" + ex.Message); } } finally { } return bHYFlag; } } }
