using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Diagnostics; public class SqlClass { protected SqlConnection Connection; protected string connectionstring; public SqlClass() { connectionstring = "Server=(local);Database=LightA;uid=sa;pwd=;"; Connection = new SqlConnection(connectionstring); } public SqlClass(string connstring) { connectionstring = connstring; Connection = new SqlConnection(connectionstring); } /// <summary> /// 得到SQL连接 /// </summary> public SqlConnection GetConnection() { if (Connection.State != ConnectionState.Open) Connection.Open(); return Connection; } public void CloseConnection() { if (Connection.State != ConnectionState.Closed) Connection.Close(); } /*执行返回结果和不返回结果的存储过程方法*/ /// <summary> /// 根据存储过程名称和参数生成对应的SQL命令对象 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> private SqlCommand BuilderQueryCommand(string storedProcName, SqlParameter[] parameters) { SqlCommand command = new SqlCommand(); command.Connection = Connection; command.CommandText = storedProcName.Trim(); command.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (SqlParameter p in parameters) { command.Parameters.Add(p); } } return command; } /// <summary> /// 返回结果的存储过程 /// </summary> public SqlDataReader GetDataReaderByProc(string storedProcName, SqlParameter[] parameters) { SqlDataReader reader; SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters); if (Connection.State == ConnectionState.Closed) { Connection.Open(); } reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } /// <summary> /// 不返回结果的存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">参数值</param> /// <returns></returns> public int GetEffect(string storedProcName, SqlParameter[] parameters) { int result = 0; try { if (Connection.State == ConnectionState.Closed) { Connection.Open(); } SqlTransaction trans = Connection.BeginTransaction(); try { SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters); cmd.Transaction = trans; result = cmd.ExecuteNonQuery(); trans.Commit(); Connection.Close(); return result; } catch (Exception ex) { if (trans != null) { trans.Rollback(); return result; } throw ex; } finally { if (trans != null) { trans.Dispose(); } Connection.Close(); } } catch (Exception ex1) { Debug.WriteLine(ex1.ToString()); return 0; //throw new Exception(ex1.Message); } } /// <summary> /// 返回dateSet /// </summary> /// <param name="ProcName"></param> /// <param name="parameters"></param> /// <param name="tableName"></param> /// <returns></returns> public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, string tableName) { try { DataSet ds = new DataSet(); if (Connection.State == ConnectionState.Closed) { Connection.Open(); } SqlDataAdapter myDa = new SqlDataAdapter(); myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters); myDa.Fill(ds, tableName); return ds; } catch { return null; } finally { Connection.Close(); } } /// <summary> /// 自定义分页 /// </summary> /// <param name="ProcName">存储过程名</param> /// <param name="parameters">参数名</param> /// <param name="start">起始页</param> /// <param name="maxRecord">记录数</param> /// <param name="tableName">表名</param> /// <returns></returns> public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, int start, int maxRecord, string tableName) { try { DataSet ds = new DataSet(); if (Connection.State == ConnectionState.Closed) { Connection.Open(); } Connection.Open(); SqlDataAdapter myDa = new SqlDataAdapter(); myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters); myDa.Fill(ds, start, maxRecord, tableName); return ds; } catch { Connection.Close(); return null; } finally { Connection.Close(); } } /// <summary> /// 返回object类型 比如首行首列 /// </summary> /// <param name="storedProcName">存储过程名称</param> /// <param name="parameters">参数列表</param> /// <returns></returns> public object GetObjectByProc(string storedProcName, SqlParameter[] parameters) { object result = null; if (Connection.State == ConnectionState.Closed) { Connection.Open(); } // SqlTransaction trans = Connection.BeginTransaction(); try { SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters); // cmd.Transaction = trans; result = cmd.ExecuteScalar(); // trans.Commit(); Connection.Close(); return result; } catch { // trans.Rollback(); return result; } finally { Connection.Close(); } } /// <summary> /// 通过ID号操作某条记录,比如删除ID号的记录 /// </summary> /// <param name="ProcName">存储过程名</param> /// <param name="id">值</param> /// <param name="paraname">参数名如:@ID</param> /// <returns></returns> public int RunProcByID(string ProcName, int id, string paraname) { try { SqlParameter[] p ={ new SqlParameter(paraname, SqlDbType.Int) }; p[0].Value = id; return GetEffect(ProcName, p); } catch { Connection.Close(); return 0; } finally { Connection.Close(); } } /// <summary> /// 修改ID号为keyValue的记录的ParaDieldName字段的值为FiledValue /// </summary> /// <param name="ProcName">相信过程名</param> /// <param name="keyValue">ID号的值</param> /// <param name="paraKeyName">ID号对应的字段名</param> /// <param name="paraFieldName">要修改的字段名</param> /// <param name="FieldValue">要修改字段的值</param> /// <param name="length">字段的长度</param> /// <returns></returns> public int UpdateByID(string ProcName, int keyValue, string paraKeyName, string paraFieldName, string FieldValue, int length) { try { SqlParameter[] p ={ new SqlParameter(paraFieldName,SqlDbType.VarChar,length), new SqlParameter(paraFieldName, SqlDbType.Int) }; p[0].Value = FieldValue; p[1].Value = keyValue; return GetEffect(ProcName, p); } catch { Connection.Close(); return 0; } finally { Connection.Close(); } } #region 执行SQL,返回DataSet /// <summary> /// 执行SQL语句返回DataSet /// </summary> public DataSet GetDataSet(string strSql) { try { DataSet ds = new DataSet(); if (Connection.State == ConnectionState.Closed) { Connection.Open(); } SqlDataAdapter myDa = new SqlDataAdapter(); myDa.SelectCommand = new SqlCommand(strSql, Connection); myDa.Fill(ds); return ds; } catch { return null; } finally { Connection.Close(); } } #endregion #region 执行SQL,返回影响行数 /// <summary> /// 执行SQL语句返回影响行数 /// </summary> public int RunSql(string strSql) { int effect = 0; if (Connection.State == ConnectionState.Closed) { Connection.Open(); } SqlTransaction trans = Connection.BeginTransaction(); try { SqlCommand cmd = new SqlCommand(strSql, Connection); cmd.Transaction = trans; effect = cmd.ExecuteNonQuery(); trans.Commit(); return effect; } catch { trans.Rollback(); Connection.Close(); return effect; } finally { Connection.Close(); } } #endregion #region 执行SQL语句返回SqlDataReader /// <summary> /// 执行SQL语句返回SqlDataReader /// </summary> public SqlDataReader GetDataReader(string strsql) { if (Connection.State == ConnectionState.Closed) { Connection.Open(); } try { SqlCommand cmd = new SqlCommand(strsql, Connection); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { Connection.Close(); return null; } } #endregion #region 执行SQL,返回首行首列 /// <summary> /// 执行SQL语句返回object /// </summary> public object GetObject(string strsql) { if (Connection.State == ConnectionState.Closed) { Connection.Open(); } object obj = null; try { SqlCommand cmd = new SqlCommand(strsql, Connection); obj = cmd.ExecuteScalar(); Connection.Close(); return obj; } catch { Connection.Close(); return null; } finally { Connection.Close(); } } #endregion #region 执行SQL,得到DataTable public DataTable GetDataTable(string strSql) { DataTable dt = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter(); try { SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = strSql; cmd.Connection = GetConnection(); sda.SelectCommand = cmd; sda.Fill(dt); } catch (Exception e) { Debug.WriteLine(e.ToString()); CloseConnection(); } finally { CloseConnection(); } return dt; } #endregion }
使用举例:
SqlClass sql = new SqlClass();
SqlDataReader dr = sql.GetDataReader("select * from"); if (dr != null) { while (dr.Read()) { if (dr["RecvTime"] != DBNull.Value) { //...... } } dr.Close(); dr.Dispose(); }