话不多少 直接贴代码
需引入的命名空间
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
定义连接属性
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 private SqlConnection conn; 2 public SqlConnection Conn 3 { 4 get 5 { 6 if (conn == null) 7 { 8 string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 9 conn = new SqlConnection(connStr); 10 } 11 if (conn.State == ConnectionState.Closed) 12 conn.Open(); 13 if (conn.State == ConnectionState.Broken) 14 { 15 conn.Close(); 16 conn.Open(); 17 } 18 return conn; 19 } 20 }
做查询(包括sql语句和带参数的SQL语句)
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //查询:DataReader 2 public SqlDataReader GetReader(string sql) 3 { 4 SqlCommand cmd = new SqlCommand(sql, Conn); 5 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 6 } 7 public SqlDataReader GetReader(string sql,SqlParameter[] paras) 8 { 9 SqlCommand cmd = new SqlCommand(sql, Conn); 10 cmd.Parameters.AddRange(paras); 11 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 12 } 13 14 //查询:DataTable 15 public DataTable GetTable(string sql) 16 { 17 SqlDataAdapter dap = new SqlDataAdapter(sql, Conn); 18 DataTable dt = new DataTable(); 19 dap.Fill(dt); 20 conn.Close(); 21 return dt; 22 } 23 public DataTable GetTable(string sql, SqlParameter[] paras) 24 { 25 SqlCommand cmd = new SqlCommand(sql, Conn); 26 cmd.Parameters.AddRange(paras); 27 SqlDataAdapter dap = new SqlDataAdapter(cmd); 28 DataTable dt = new DataTable(); 29 dap.Fill(dt); 30 conn.Close(); 31 return dt; 32 33 }
做增删改(包括sql语句和带参数的SQL语句)
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //增改删 2 public bool ExecuteNoQuery(string sql) 3 { 4 SqlCommand cmd = new SqlCommand(sql, Conn); 5 int result = cmd.ExecuteNonQuery(); 6 this.conn.Close(); 7 return result > 0; 8 } 9 public bool ExecuteNoQuery(string sql, SqlParameter[] paras) 10 { 11 SqlCommand cmd = new SqlCommand(sql, Conn); 12 cmd.Parameters.AddRange(paras); 13 int result = cmd.ExecuteNonQuery(); 14 this.conn.Close(); 15 return result > 0; 16 }
执行聚合函数(包括sql语句和带参数的SQL语句)
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //执行聚合函数 2 public object ExecuteScalar(string sql) 3 { 4 SqlCommand cmd = new SqlCommand(sql, Conn); 5 object result = cmd.ExecuteScalar(); 6 this.conn.Close(); 7 return result; 8 } 9 public object ExecuteScalar(string sql, SqlParameter[] paras) 10 { 11 SqlCommand cmd = new SqlCommand(sql, Conn); 12 cmd.Parameters.AddRange(paras); 13 object result = cmd.ExecuteScalar(); 14 this.conn.Close(); 15 return result; 16 }
执行存储过程获取数据集(包括sql语句和带参数的SQL语句)
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //执行存储过程获取数据集(查询) 2 public DataTable ExecuteProcSelect(string ProcName, SqlParameter[] paras) 3 { 4 SqlCommand cmd = new SqlCommand(ProcName, Conn); 5 cmd.Parameters.AddRange(paras); 6 cmd.CommandType = CommandType.StoredProcedure; 7 SqlDataAdapter dap = new SqlDataAdapter(cmd); 8 DataTable dt = new DataTable(); 9 dap.Fill(dt); 10 this.conn.Close(); 11 return dt; 12 }
执行存储过程做增删改
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //执行存储过程做增删改 2 public bool ExecuteProcUpdate(string ProcName, SqlParameter[] paras) 3 { 4 SqlCommand cmd = new SqlCommand(ProcName, Conn); 5 cmd.Parameters.AddRange(paras); 6 cmd.CommandType = CommandType.StoredProcedure; 7 int result = cmd.ExecuteNonQuery(); 8 this.conn.Close(); 9 return result > 0; 10 }
执行事物(ADO.NET)
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //执行事物(ADO.NET) 2 public bool ExecuteTrasaction(string sqlStr, string TranName) 3 { 4 bool result = true; 5 SqlTransaction tran = null; 6 try 7 { 8 tran = Conn.BeginTransaction(TranName); 9 SqlCommand cmd = new SqlCommand(sqlStr, Conn, tran); 10 int n = cmd.ExecuteNonQuery(); 11 if (n > 0) 12 { 13 tran.Commit(); 14 result = true; 15 } 16 else 17 { 18 tran.Rollback(); 19 result = false; 20 } 21 } 22 catch 23 { 24 tran.Rollback(); 25 result = false; 26 } 27 return result; 28 }
本文完...