using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public DbHelperSQL() { } #region 公用方法 /// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tableName">表名称</param> /// <param name="columnName">列名称</param> /// <returns>是否存在</returns> public static bool ColumnExists(string tableName, string columnName) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; object res = GetSingle(sql); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } public static int GetMinID(string FieldName, string TableName) { string strsql = "select min(" + FieldName + ") from " + TableName; object obj = DbHelperSQL.GetSingle(strsql); if (obj == null) { return 0; } else { return int.Parse(obj.ToString()); } } public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = DbHelperSQL.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = DbHelperSQL.GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 表是否存在 /// </summary> /// <param name="TableName"></param> /// <returns></returns> public static bool TabExists(string TableName) { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; object obj = DbHelperSQL.GetSingle(strsql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(string strSql, params SqlParameter[] cmdParms) { object obj = DbHelperSQL.GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 2012-2-21新增重载，执行SQL语句，返回影响的记录数 /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">SqlTransaction事件</param> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { cmd.Connection = connection; cmd.Transaction = trans; int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { //trans.Rollback(); throw e; } } } public static int ExecuteSqlByTime(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(List<String> SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object ExecuteSqlGet(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } public static object GetSingle(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// <summary> /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, connection); try { connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw e; } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } public static DataSet Query(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 2012-2-21新增重载，执行查询语句，返回DataSet /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">SqlTransaction事务</param> /// <param name="SQLString">SQL语句</param> /// <returns>DataSet</returns> public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString) { DataSet ds = new DataSet(); try { SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.SelectCommand.Transaction = trans; command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } #endregion #region 执行带参数的SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } /// <summary> /// 2012-2-29新增重载，执行SQL语句，返回影响的记录数 /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">SqlTransaction对象</param> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, trans, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException e) { //trans.Rollback(); throw e; } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch(Exception ex) { trans.Rollback(); throw; } } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } /// <summary> /// 2012-2-21新增重载，执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">SqlTransaction事务</param> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, trans, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { //trans.Rollback(); throw e; } } } /// <summary> /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw e; } // finally // { // cmd.Dispose(); // connection.Close(); // } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } } /// <summary> /// 2012-2-21新增重载，执行查询语句，返回DataSet /// </summary> /// <param name="connection">SqlConnection对象</param> /// <param name="trans">SqlTransaction事务</param> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, trans, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { //trans.Rollback(); throw new Exception(ex.Message); } return ds; } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion #region 存储过程操作 /// <summary> /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); return returnReader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.CommandTimeout = Times; sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; } /// <summary> /// 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// <summary> /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand 对象实例</returns> private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } #endregion }
配置文件
<?xml version="1.0" encoding="utf-8" ?> <configuration> <!-- 数据库连接字符串--> <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=qrab;Integrated Security=True;" /> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" /> </startup> </configuration>
用法：
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Windows.Forms; using System.Collections; namespace WindowsFormsApp3 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } /// <summary> /// DbHelperSQL.ExecuteReader取一条数据(两重重载) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { //用法一 SqlDataReader read = DbHelperSQL.ExecuteReader("select id,name,txt from qrtable where id=2"); while (read.Read()) { MessageBox.Show(read["id"].ToString()); MessageBox.Show(read["name"].ToString()); MessageBox.Show(read["txt"].ToString()); } read.Close(); //用法二 StringBuilder builder = new StringBuilder(); builder.Append("select id,name,txt from qrtable where id=@id and txt=@txt"); SqlParameter[] paramet = { new SqlParameter("@id",SqlDbType.Int,4), new SqlParameter("@txt",SqlDbType.NVarChar,255) }; paramet[0].Value = 3; paramet[1].Value = "333"; SqlDataReader read2 = DbHelperSQL.ExecuteReader(builder.ToString(), paramet); while (read2.Read()) { MessageBox.Show(read2["id"].ToString()); MessageBox.Show(read2["name"].ToString()); MessageBox.Show(read2["txt"].ToString()); } read2.Close(); } /// <summary> /// DbHelperSQL.ExecuteSql插入|修改|删除(五重重载) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { //用法一 int result = DbHelperSQL.ExecuteSql("INSERT INTO qrtable(name,txt) VALUES('qrcode','qrlx')"); MessageBox.Show(result.ToString()); //用法二 //DbHelperSQL.ExecuteSql("", "");//未完成 //用法三 SqlParameter name = new SqlParameter("@name", SqlDbType.NVarChar, 255);name.Value = "zg"; SqlParameter txt = new SqlParameter(@"txt", SqlDbType.NVarChar, 255);txt.Value = "txt"; SqlParameter[] paramet = { name, txt }; int result1 = DbHelperSQL.ExecuteSql("INSERT INTO qrtable(name,txt) VALUES(@name,@txt)", paramet); MessageBox.Show(result1.ToString()); //用法四 //DbHelperSQL.ExecuteSql(SqlConnection,SqlTransaction,"sql")//未完成 //用法五 //DbHelperSQL.ExecuteSql(SqlConnection, SqlTransaction, "sql", parameter);//未完成 } /// <summary> /// DbHelperSQL.ExecuteSqlByTime插入|修改|删除(设置超时时间) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { DbHelperSQL.ExecuteSqlByTime("INSERT INTO table(name,txt) VALUES('qrcode','qrlx')", 300); } /// <summary> /// DbHelperSQL.ExecuteSqlGet插入(返回受影响的行) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button4_Click(object sender, EventArgs e) { DbHelperSQL.ExecuteSqlGet("", ""); } /// <summary> /// DbHelperSQL.ExecuteSqlInsertImg(存储二进制格式到数据库) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button5_Click(object sender, EventArgs e) { //DbHelperSQL.ExecuteSqlInsertImg(string, byte); } /// <summary> /// DbHelperSQL.ExecuteSqlTran执行多条SQL语句(支持事务回滚) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button6_Click(object sender, EventArgs e) { //用法一:参数化执行多条SQL语句(支持事务回滚) Hashtable hashtable = new Hashtable(); StringBuilder build1 = new StringBuilder(); build1.Append("INSERT INTO qrtable(name,txt) VALUES(@name,@txt)"); SqlParameter[] parent1 = { new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter("@txt",SqlDbType.NVarChar,255) }; parent1[0].Value = "haha111"; parent1[1].Value = "hahaha111"; hashtable.Add(build1, parent1); StringBuilder build2 = new StringBuilder(); build2.Append("UPDATE qrtable SET name=@name,txt=@txt WHERE id=@id"); SqlParameter[] parent2 = { new SqlParameter("@id",SqlDbType.Int,8), new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter(@"txt",SqlDbType.NVarChar,255) }; parent2[0].Value = 1; parent2[1].Value = "xx"; parent2[2].Value = "xxx"; hashtable.Add(build2, parent2); DbHelperSQL.ExecuteSqlTran(hashtable); //用法二:执行多条SQL语句字符串拼接方式 List<string> list = new List<string>(); string sql1 = "INSERT INTO table(name,txt) VALUES('qrcode','qrlx')"; string sql2 = "INSERT INTO table(name,txt) VALUES('qrcode1','qrlx1')"; string sql3 = "UPDATE table SET name='汽车' txt='zqrios' WHERE id=1"; list.Add(sql1); list.Add(sql2); list.Add(sql3); DbHelperSQL.ExecuteSqlTran(list); //用法三:未完成 //List<CommandInfo> info = new List<CommandInfo>(); //CommandInfo oneinfo = new CommandInfo(); //info.Add(oneinfo); //DbHelperSQL.ExecuteSqlTran(info); } /// <summary> /// DbHelperSQL.GetSingle根据条件查询(返回单个对象) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button7_Click(object sender, EventArgs e) { SqlParameter[] paramet ={ //new SqlParameter("@id",SqlDbType.Int,4), new SqlParameter("@txt",SqlDbType.NVarChar,255) }; //paramet[0].Value = 1; paramet[0].Value = "txt"; object obj = DbHelperSQL.GetSingle("select count(id) from qrtable where txt=@txt", paramet); } /// <summary> /// DbHelperSQL.Query条件查询(返回数据集dataset) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button8_Click(object sender, EventArgs e) { SqlParameter[] paramet ={ new SqlParameter("@id",SqlDbType.Int,4), //new SqlParameter("@txt",SqlDbType.NVarChar,255) }; paramet[0].Value = 1; //paramet[1].Value = "qr"; DataSet ds = DbHelperSQL.Query("select id,name,txt from qrtable where id=@id", paramet); } /// <summary> /// DbHelperSQL.RunProcedure增删改查执行存储过程 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button9_Click(object sender, EventArgs e) { //用法一返回单条数据 IDataParameter[] iparamt1 = new IDataParameter[] { new SqlParameter("@id",SqlDbType.Int,4) }; iparamt1[0].Value = 1; SqlDataReader read = DbHelperSQL.RunProcedure("qr_code", iparamt1); while (read.Read()) { MessageBox.Show(read["name"].ToString()); } read.Close(); //用法二 执行存储过程，插入\修改\删除\返回影响的行数 IDataParameter[] iparamt2 = new IDataParameter[]{ new SqlParameter("@id",SqlDbType.Int,4) }; iparamt2[0].Value = 1; string rowNumber = ""; DbHelperSQL.RunProcedure("qr_codeint", iparamt2, rowNumber); //用法三 返回数据集dataset IDataParameter[] iparamt3 = new IDataParameter[]{ new SqlParameter("@id",SqlDbType.Int,4) }; iparamt3[0].Value = 1; DataSet ds = DbHelperSQL.RunProcedure("qr_codelist", iparamt3, "qrcode"); } } }
存储过程操作用列
private void button9_Click(object sender, EventArgs e) { //1.用法一返回单条数据 //存储过程start //create proc proc_id //@id int //as //select* from qrtable where id = @id //go //存储过程end IDataParameter[] iparamt1 = new IDataParameter[] { new SqlParameter("@id",SqlDbType.Int,4) }; iparamt1[0].Value = 9; SqlDataReader read = DbHelperSQL.RunProcedure("proc_id", iparamt1); while (read.Read()) { MessageBox.Show(read["name"].ToString()); MessageBox.Show(read["txt"].ToString()); } read.Close(); //2.用法二 执行存储过程，插入\修改\删除\返回影响的行数 //2.1 插入数据(insert) //存储过程start //create proc ininsert //@name nvarchar(255), //@txt nvarchar(255) //as //insert into qrtable(name, txt) values(@name, @txt) //go //存储过程end IDataParameter[] iinsert = new IDataParameter[] { new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter("@txt",SqlDbType.NVarChar,255) }; iinsert[0].Value = "gaga"; iinsert[1].Value = "jjjjjj"; int res = 0; int insertresult = DbHelperSQL.RunProcedure("ininsert", iinsert, out res); //2.2 修改数据(update) //create proc upupdate //@name nvarchar(255), //@txt nvarchar(255), //@id int //as //update qrtable set name = @name,txt = @txt where id = @id //go IDataParameter[] upupdate = new IDataParameter[] { new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter("@txt",SqlDbType.NVarChar,255), new SqlParameter("@id",SqlDbType.Int) }; upupdate[0].Value = "xxxxxxxx"; upupdate[1].Value = "txt11111"; upupdate[2].Value = 1; int res = 0; int insertresult = DbHelperSQL.RunProcedure("upupdate", upupdate, out res); //2.3 删除数据(delete) //create proc dedelete //@id int //as //delete from qrtable where id = @id //go IDataParameter[] dedelete = new IDataParameter[] { new SqlParameter("@id",SqlDbType.Int) }; dedelete[0].Value = 1; int res = 0; int deleteresult = DbHelperSQL.RunProcedure("dedelete", dedelete, out res); IDataParameter[] dedelete = new IDataParameter[] { new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter("@txt",SqlDbType.NVarChar,255), new SqlParameter("@ReturnValue",SqlDbType.Int) }; dedelete[0].Value = "zg"; dedelete[1].Value = "txt"; int res = 0; int deleteresult = DbHelperSQL.RunProcedure("userlogin", dedelete, out res); //用法三 返回数据集dataset //==存储过程start== //create proc proc_stu //@name nvarchar(255), //@txt nvarchar(255) //as //select* from qrtable where name = @name and txt = @txt //go //==存储过程end== IDataParameter[] iparamt3 = new IDataParameter[]{ //new SqlParameter("@id",SqlDbType.Int,4) new SqlParameter("@name",SqlDbType.NVarChar,255), new SqlParameter("@txt",SqlDbType.NVarChar,255) }; iparamt3[0].Value = "xx"; iparamt3[1].Value = "txt"; DataSet ds = DbHelperSQL.RunProcedure("proc_stu", iparamt3, "qrcode"); }
