/// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { DbHelperSQL db = new HelpereStr(); using (SqlConnection conn = new SqlConnection(db.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 { trans.Rollback(); throw; } } } }
/// <summary> /// 批量插入 /// </summary> /// <param name="dt"></param> /// <param name="tablename"></param> /// <returns></returns> public static bool SqlBulkCopyInsert(DataTable dt, string tablename, string connectionStringName) { bool flag = false; DbHelperSQL db = new HelpereStr(connectionStringName); SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(db.connectionString, SqlBulkCopyOptions.UseInternalTransaction); sqlBulkCopy.BulkCopyTimeout = 5000; sqlBulkCopy.NotifyAfter = dt.Rows.Count; sqlBulkCopy.BatchSize = dt.Rows.Count; try { if (dt != null && dt.Rows.Count != 0) { sqlBulkCopy.DestinationTableName = tablename; sqlBulkCopy.WriteToServer(dt); } flag = true; } catch (Exception ex) { flag = false; } finally { sqlBulkCopy.Close(); } return(flag); }
/// <summary> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataTable QueryTable(string connectionStringName, string SQLString) { DbHelperSQL db = new HelpereStr(connectionStringName); using (SqlConnection connection = new SqlConnection(db.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); } if (ds != null && ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0) { return(ds.Tables[0]); } else { return(null); } } }
public static object GetSingle(string SQLString, int Times) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object ExecuteSqlGet(string SQLString, string content) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(List <String> SQLStringList) { DbHelperSQL db = new HelpereStr(); using (SqlConnection conn = new SqlConnection(db.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> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { DbHelperSQL db = new HelpereStr(); SqlConnection connection = new SqlConnection(db.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="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.connectionString)) { DataSet ds = new DataSet(); connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); return(ds); } }
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { DbHelperSQL db = new HelpereStr(); using (SqlConnection conn = new SqlConnection(db.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> /// 批量插入更新 /// </summary> /// <param name="ds"></param> /// <param name="strTblName"></param> /// <param name="strConnection"></param> /// <returns></returns> public static int UpdateByDataSet(DataTable dt, string strTblName) { try { DbHelperSQL db = new HelpereStr(); SqlConnection connection = new SqlConnection(db.connectionString); connection.Open(); SqlDataAdapter ada = new SqlDataAdapter(("select * from " + strTblName), connection); SqlCommandBuilder cb = new SqlCommandBuilder(ada); return(ada.Update(dt)); } catch (Exception errBU) { throw errBU; } }
/// <summary> /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) { DbHelperSQL db = new HelpereStr(); SqlConnection connection = new SqlConnection(db.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> /// 批量插入 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static int SqlBulkCopy(DataTable dt) { DbHelperSQL db = new HelpereStr(); SqlBulkCopy sbc = new SqlBulkCopy(db.connectionString, SqlBulkCopyOptions.UseInternalTransaction); sbc.BulkCopyTimeout = 5000; sbc.NotifyAfter = dt.Rows.Count; try { sbc.DestinationTableName = "bcd"; sbc.WriteToServer(dt); } catch (Exception ex) { } return(sbc.NotifyAfter); }
public static DataSet Query(string connectionStringName, string SQLString) { DbHelperSQL db = new HelpereStr(connectionStringName); using (SqlConnection connection = new SqlConnection(db.connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { } return(ds); } }
public static DataSet Query(string SQLString, int Times) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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> /// 批量插入 /// </summary> /// <param name="dt"></param> /// <param name="tablename"></param> /// <returns></returns> public static int SqlBulkCopyInsert(DataTable dt, string tablename) { DbHelperSQL db = new HelpereStr(); SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(db.connectionString, SqlBulkCopyOptions.UseInternalTransaction); sqlBulkCopy.BulkCopyTimeout = 5000; sqlBulkCopy.NotifyAfter = dt.Rows.Count; sqlBulkCopy.BatchSize = dt.Rows.Count; try { if (dt != null && dt.Rows.Count != 0) { sqlBulkCopy.DestinationTableName = tablename; sqlBulkCopy.WriteToServer(dt); } } catch (Exception ex) { } return(sqlBulkCopy.NotifyAfter); }
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlByConnStr(string connectionStringName, string SQLString) { DbHelperSQL db = new HelpereStr(connectionStringName); using (SqlConnection connection = new SqlConnection(db.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> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string connectionStringName, string SQLString, params SqlParameter[] cmdParms) { DbHelperSQL db = new HelpereStr(connectionStringName); using (SqlConnection connection = new SqlConnection(db.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> /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { DbHelperSQL db = new HelpereStr(); SqlConnection connection = new SqlConnection(db.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) { DbHelperSQL db = new HelpereStr(); using (SqlConnection connection = new SqlConnection(db.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); } } }