/// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(Base_Helper.PetaPoco.Database PCDb, string strSQL, byte[] fs) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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(); } } }
public static object GetSingle(Base_Helper.PetaPoco.Database PCDb, string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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> /// 执行一条计算查询结果语句,返回查询结果(object)。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果(object)</returns> public static object GetSingle(Base_Helper.PetaPoco.Database PCDb, string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static object ExecuteSqlGet(Base_Helper.PetaPoco.Database PCDb, string SQLString, string content) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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(Base_Helper.PetaPoco.Database PCDb, string SQLString, string content) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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(Base_Helper.PetaPoco.Database PCDb, List <String> SQLStringList) { using (SqlConnection conn = new SqlConnection(PCDb._helperconnectionString)) { 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="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static void ExecuteSqlTran(Base_Helper.PetaPoco.Database PCDb, Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(PCDb._helperconnectionString)) { 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="proName">存储过程的名称</param> /// <param name="parameters">存储过程中的参数数组</param> /// <returns>输出参数</returns> public static string ExecuteProcedure(Base_Helper.PetaPoco.Database PCDb, string storedProcName, SqlParameter[] parameters) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); command.ExecuteNonQuery(); return(command.Parameters[parameters.Length - 1].SqlValue.ToString()); //将输出参数的值赋予临时变量,用以返回 } }
/// <summary> /// 判断是否存在某表的某个字段 /// </summary> /// <param name="tableName">表名称</param> /// <param name="columnName">列名称</param> /// <returns>是否存在</returns> public static bool ColumnExists(Base_Helper.PetaPoco.Database PCDb, string tableName, string columnName) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; object res = GetSingle(PCDb, sql); if (res == null) { return(false); } return(Convert.ToInt32(res) > 0); }
/// <summary> /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader RunProcedure(Base_Helper.PetaPoco.Database PCDb, string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(PCDb._helperconnectionString); 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(Base_Helper.PetaPoco.Database PCDb, string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return(result); } }
/// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(Base_Helper.PetaPoco.Database PCDb, string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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 int GetMaxID(Base_Helper.PetaPoco.Database PCDb, string FieldName, string TableName) { string strsql = "select max(" + FieldName + ") from " + TableName; object obj = PetaSQLHelper.GetSingle(PCDb, strsql); if (obj == null) { return(1); } else { return(int.Parse(obj.ToString())); } }
/// <summary> /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(Base_Helper.PetaPoco.Database PCDb, string strSQL) { SqlConnection connection = new SqlConnection(PCDb._helperconnectionString); 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(Base_Helper.PetaPoco.Database PCDb, string SQLString, string DataTableName) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, DataTableName); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return(ds); } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static void ExecuteSqlTranWithIndentity(Base_Helper.PetaPoco.Database PCDb, Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(PCDb._helperconnectionString)) { 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> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(Base_Helper.PetaPoco.Database PCDb, string SQLString) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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(Base_Helper.PetaPoco.Database PCDb, string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { 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> /// 执行查询语句,返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(Base_Helper.PetaPoco.Database PCDb, string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(PCDb._helperconnectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return(ds); } } }
/// <summary> /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(Base_Helper.PetaPoco.Database PCDb, string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(PCDb._helperconnectionString); 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(); // } }
public static bool Exists(Base_Helper.PetaPoco.Database PCDb, string strSql) { object obj = PetaSQLHelper.GetSingle(PCDb, 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(Base_Helper.PetaPoco.Database PCDb, 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 = PetaSQLHelper.GetSingle(PCDb, 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> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public static int ExecuteSqlTran(Base_Helper.PetaPoco.Database PCDb, System.Collections.Generic.List <CommandInfo> cmdList) { using (SqlConnection conn = new SqlConnection(PCDb._helperconnectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int count = 0; //循环 foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return(0); } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return(0); } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return(0); } continue; } int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return(0); } cmd.Parameters.Clear(); } trans.Commit(); return(count); } catch { trans.Rollback(); throw; } } } }