SQlServer(2)封装SqlHelper(不包含事务)
封装
/************************************************* * 描述: * * Author:yuanshuo * Date:2021/11/18 10:45:26 * Update: * ************************************************/ using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Common.SqlServerHelper { public static class SqlServerHelper { //从配置文件中获取连接字符串 readonly修饰的变量只能在初始化或构造函数中赋值;其他地方只能读取 public static string constr = CommonConfiger.DBConStr; //private static readonly string constr = "Data Source=DELLYS;Initial Catalog=ceshi;User=sa;PassWord=123"; //private static SqlConnection con = new SqlConnection(constr); /// <summary> /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql, CommandType cmdtype, params SqlParameter[] pms) { int i = -1; try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } if (con.State != ConnectionState.Open) { con.Open(); } i = cmd.ExecuteNonQuery(); } } } catch (Exception) { throw; } return i; } /// <summary> /// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static object ExecuteScalar(string sql, CommandType cmdtype, params SqlParameter[] pms) { try { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } if (con.State != ConnectionState.Open) { con.Open(); } object result = cmd.ExecuteScalar(); con.Close(); return result; } } } catch (Exception ex) { throw; } } /// <summary> /// 执行查询,返回多行、多列的方法ExecuteReader() /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(string sql, CommandType cmdtype, params SqlParameter[] pms) { SqlConnection con = new SqlConnection(constr); using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandTimeout = 60000000; //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } try { if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch { con.Close(); con.Dispose(); throw; } } } /// <summary> /// 执行返回多个查询时使用,返回DataTable类型 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms">参数</param> /// <returns></returns> public static DataTable ExecuteDataTable(string sql, CommandType cmdtype, params SqlParameter[] pms) { DataTable dt = new DataTable(); try { //通过adapter读取数据。 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr)) { adapter.SelectCommand.CommandType = cmdtype; if (pms != null) { adapter.SelectCommand.Parameters.AddRange(pms); } adapter.Fill(dt); return dt; } } catch (Exception) { throw; } finally { } } /// <summary> /// 该方法是一个数据集方法在很多地方用得到的 /// </summary> /// <param name="sql">第一个参数是传sql语句</param> /// <param name="table">第二个参数是传数据库当中的表名</param> /// <returns></returns> public static DataSet GetDataSet(string sql, CommandType cmdtype, string table) { try { using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr)) { adapter.SelectCommand.CommandType = cmdtype; DataSet ds = new DataSet(); adapter.Fill(ds, table); return ds; } } catch (Exception) { throw; } } /// <summary> /// DataTable批量插入 /// </summary> /// <param name="dt"></param> /// <param name="emsg"></param> /// <returns></returns> public static bool GetInsertByDataTable(DataTable dt, ref string emsg) { try { SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(constr, SqlBulkCopyOptions.FireTriggers); sqlbulkcopy.DestinationTableName = dt.TableName; foreach (DataColumn dc in dt.Columns) { sqlbulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } sqlbulkcopy.WriteToServer(dt); return true; } catch (Exception ex) { emsg = ex.Message; return false; } } /// <summary> /// 连接数据 /// </summary> /// <param name="constring"></param> /// <returns></returns> public static bool Open(string constring = null) { if (constring == null) { constring = constr; } SqlConnection con = new SqlConnection(constring); try { if (con.State != ConnectionState.Open) { con.Open(); } if (con.State == ConnectionState.Open) { return true; } else { return false; } } catch { con.Close(); con.Dispose(); return false; } } /// <summary> /// 批量更新表数据 /// </summary> /// <param name="updateTable">更新表源数据</param> /// <param name="tableName">表名称</param> /// <returns></returns> public static bool ResponseOperateSN(DataTable updateTable,string tableName,string updatePramSql) { StringBuilder sql = new StringBuilder(); if (updateTable.Rows.Count > 0) { updateTable.TableName = tableName; updateTable.AcceptChanges(); foreach (System.Data.DataRow dr in updateTable.Rows) { dr.SetModified(); } using (SqlConnection con = new SqlConnection(constr)) { // 初期化SqlCommand //SqlCommand loCmd = new SqlCommand("select * from OrderSN where 1=0", moConn, moTrans); SqlCommand loCmd = new SqlCommand("select * from OrderSN where 1=0", con); SqlDataAdapter da = new SqlDataAdapter(loCmd); SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da); da.AcceptChangesDuringUpdate = false; //SqlCommand updatecmd = new SqlCommand(" UPDATE OrderSN with(rowlock,updlock) SET ordersn_modifytime=GETDATE(),Flag='1' where OrderSN_ID= @OrderSN_ID ", con); SqlCommand updatecmd = new SqlCommand(updatePramSql, con); updatecmd.UpdatedRowSource = UpdateRowSource.None; da.UpdateCommand = updatecmd; da.UpdateCommand.Parameters.Add("@OrderSN_ID", SqlDbType.NVarChar, 64, "OrderSN_ID"); da.UpdateBatchSize = updateTable.Rows.Count; da.Update(updateTable); updateTable.AcceptChanges(); } } return true; } } }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步