SqlHelper分享
using Model; 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 DAL { public static class SqlHelper { //public static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString(); public static string conStr = "server=***;uid=sa;pwd=******;database=TripMaster"; /// <summary> /// 用一个sql语句和可变的SQLParameter数组增删改数据库的数据并且返回受影响的行数,当受影响行数为0时返回的是负数 /// </summary> /// <param name="cmdStr">sql语句</param> /// <param name="sqlParams">params形的变量数组</param> /// <returns>返回受影响的行数</returns> public static int ExecuteNonQuery(string cmdStr, params SqlParameter[] sqlParams) { using (SqlConnection con = new SqlConnection(conStr)) { using (SqlCommand cmd = new SqlCommand(cmdStr, con)) { if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } con.Open(); return cmd.ExecuteNonQuery(); } } } /// <summary> /// 使用一个sql语句来查找数据库,返回查找数据的第一行第一列的数据 /// </summary> /// <param name="cmdStr">sql语句</param> /// <param name="sqlParams">params形式的SqlParameters数组</param> /// <returns>返回所查找信息第一行第一列的数据,类型未知,所以用object接收</returns> public static object ExecuteScalar(string cmdStr, params SqlParameter[] sqlParams) { using (SqlConnection con = new SqlConnection(conStr)) { using (SqlCommand cmd = new SqlCommand(cmdStr, con)) { if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } con.Open(); return cmd.ExecuteScalar(); } } } /// <summary> /// 使用一个sql语句查找数据返回一个dataTable; /// </summary> /// <param name="cmdStr">sql语句</param> /// <param name="sqlParams">params形式的SqlParameter数组</param> /// <returns>返回一张表DataTable</returns> public static DataTable ExecuteDataTable(string cmdStr, params SqlParameter[] sqlParams) { using (SqlConnection con = new SqlConnection(conStr)) { using (SqlCommand cmd = new SqlCommand(cmdStr, con)) { con.Open(); if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); dataAdapter.Fill(dt); return dt; } } } } /// <summary> /// 使用一个sql语句查找数据库信息,返回一个SqldataReader,封装这一个方法时要注意,cmd是可以关闭的, /// 而适配器在读取数据的时候con是不能关闭的 /// 所以要在适配器的创建里面加上参数System.Data.CommandBehavior.CloseConnection, /// 这个参数的意思是当适配器关闭的时候顺便也关闭con,就是con直到不再从数据库读取数据时才可以关闭 /// </summary> /// <param name="cmdStr">sql语句</param> /// <param name="sqlParams">params形式的SqlParameters数组</param> /// <returns>返回一个SqlDatareader可以进行读取数据</returns> public static SqlDataReader ExecuteReader(string cmdStr, params SqlParameter[] sqlParams) { SqlConnection con = new SqlConnection(conStr); using (SqlCommand cmd = new SqlCommand(cmdStr, con)) { if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return reader; } catch { con.Close(); con.Dispose(); throw;//不抛出这个就会报错 } finally { } } } public static int ExcuteAddMany(string cmdStr, string[] tempArray, params SqlParameter[] sqlParams) { DataTable table = new DataTable(); table.Columns.Add("Name"); table.Columns.Add("LayerNum"); for (int i=0; i<tempArray.Length;i++) { DataRow row = table.NewRow(); row["Name"] = tempArray[i].ToString(); row["LayerNum"] = 0; table.Rows.Add(row); } string connectionStr = "server=PC-201701130905\\B;uid=sa;pwd=s07j08b11;database=TripMaster"; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr)) { bulkCopy.BatchSize = table.Rows.Count; bulkCopy.DestinationTableName = "SQLBulkCopyTest";//在插入的目标表 //DataTable列名与数据库列名的映射 bulkCopy.ColumnMappings.Add("LayerNum", "LayerNum"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.WriteToServer(table);//写入到数据库中 } return 1; } } }