数据库连接字符串:
(Copy Code)
string connectionString= System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
执行单条增删改语句,返回影响的记录数(Copy Code)
/// <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 (SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } }
执行多条增,删,改Sql语句,实现数据库事务(Copy Code)
/// <summary> /// 执行多条增,删,改Sql语句,实现数据库事务。 /// 多条增,删,改语句,如果失败将撤销操作(回滚) /// </summary> /// <param name="SqlStringList">多条Sql语句</param> public static void ExecuteSqlTran(ArrayList SqlStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SqlStringList.Count; n++) { string strSql = SqlStringList[n].ToString(); if (strSql.Trim().Length > 1) { cmd.CommandText = strSql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); } finally { cmd.Dispose(); conn.Close(); } } }
执行带一个SqlParameter参数的增,删,改Sql语句(Copy Code)
/// <summary> /// 执行带一个SqlParameter参数的的Sql语句。 /// 示例:int i=DbHelperSql.ExecuteSql("insert into operater values('1','2','3',@content)","4"); /// </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); SqlParameter myParameter = new SqlParameter("@content",SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
向数据库里插入Image的二进制数据(Copy Code)
/// <summary> /// 向数据库里插入Image的二进制数据 /// </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); SqlParameter myParameter = new SqlParameter("@image",SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } }
执行一条SQl语句,返回第一行第一列(object)(Copy Code)
/// <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 (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); connection.Close(); } } } }
执行查询语句,返回SqlDataReader(Copy Code)
/// <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); // myReader.Close();需在在使用完毕后Close(); return myReader; } catch (SqlException e) { throw new Exception(e.Message); } }
执行断开式查询,返回DataSet(Copy Code)
/// <summary> /// 执行断开式查询,返回DataSet /// </summary> /// <param name="SqlString">查询语句</param> /// <returns>DataSet</returns> public static DataSet GetDataSet(string SqlString) { SqlConnection connection = new SqlConnection(connectionString); DataSet ds = new DataSet(); try { SqlDataAdapter command = new SqlDataAdapter(SqlString, connection); command.Fill(ds, "table1"); } catch (SqlException ex) { throw new Exception(ex.Message); } return ds; }
执行查询语句,返回DataTable(Copy Code)
/// <summary> /// 执行查询语句,返回DataTable /// </summary> /// <param name="SqlString"></param> /// <param name="TableName"></param> /// <param name="ct"></param> /// <returns></returns> public static DataTable GetDataTable(string SqlString) { SqlConnection connection = new SqlConnection(connectionString); DataTable dt = new DataTable(); try { SqlDataAdapter command = new SqlDataAdapter(SqlString, connection); command.Fill(dt); } catch (SqlException ex) { throw new Exception(ex.Message); } return dt; }
根据参数传来的存储过程名和存储过程中用到的参数,执行增、删、改操作(Copy Code)
/// <summary> /// 根据参数传来的存储过程名和存储过程中用到的参数,执行增、删、改操作 /// </summary> /// <param name="strProc">存储过程名</param> /// <param name="pars">存储过程中用到的参数数组</param> public void ExecuteProcNonQuery(string strProc, SqlParameter[] pars) { SqlCommand cmd = new SqlCommand(strProc, conn); cmd.CommandType = CommandType.StoredProcedure; if (pars != null) //如果存储过程中用到的参数数组不为空 { foreach (SqlParameter par in pars) //则遍历数组 { cmd.Parameters.Add(par); //将所有的参数添加到SqlCommand对象的参数属性中 } } try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); } }
根据参数传来的存储过程名和存储过程中用到的参数,执行断开式查询操作(Copy Code)
/// <summary> /// 根据参数传来的存储过程名和存储过程中用到的参数,执行断开式查询操作 /// </summary> /// <param name="strProc">存储过程名</param> /// <param name="pars">存储过程中用到的参数</param> /// <returns>返回DataTable类型的查询结果</returns> public DataTable ExecuteProcQuery(string strProc, SqlParameter[] pars) { DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(strProc, conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; if (pars != null) //如果存储过程中用到的参数数组不为空 { foreach (SqlParameter par in pars) //则遍历数组 { da.SelectCommand.Parameters.Add(par); //将所有的参数添加到SqlDataAdapter对象的参数属性中 } } try { da.Fill(dt); } catch (Exception ex) { throw new Exception(ex.Message); } return dt; }