求一段操作数据(增删改查)的代码
求一段操作数据的代码
要求是通用的，不管字段多少，数据类型、长度,单表的即可
[解决办法]
增：Insert into tablename values(...)
刪:Delete from Tablename where(...)
改：Update Tablename Set(...) where(...)
保証通用
[解决办法]
public class Connection {
private string connStr;
private SqlTransaction tran;
public SqlConnection conn;
private SqlCommand cmd;
public Connection() {
connStr = ConfigurationSettings.AppSettings[ "ConnStr "];
conn = new SqlConnection(connStr);
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open() {
if(conn!=null) {
conn.Close();
}
conn.Open();
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close() {
if(conn!=null) {
conn.Close();
}
}
/// <summary>
/// 开始事务
/// </summary>
public void TranBegin() {
Open();
tran = conn.BeginTransaction();
}
/// <summary>
/// 回滚事务
/// </summary>
public void TranRollback(){
tran.Rollback();
if (conn != null) {
conn.Dispose(); // 关闭数据库
conn.Close(); // 关系数据库
}
}
/// <summary>
/// 回滚事务到指定点
/// </summary>
/// <param name= "tranName "> 指定点 </param>
public void TranRollback(string tranName){
tran.Rollback(tranName);
if (conn != null) {
conn.Dispose(); // 关闭数据库
conn.Close(); // 关系数据库
}
}
/// <summary>
/// 结束事务
/// </summary>
public void TranEnd() {
tran.Commit(); // 结束事务
if (conn != null) {
conn.Dispose(); // 关闭数据库
conn.Close(); // 关系数据库
}
}
/// <summary>
/// 根据SQL语句返回包含结果集的数据表
/// </summary>
/// <param name= "sql "> SELECT类型的SQL语句 </param>
/// <returns> 返回数据表 </returns>
public DataTable GetDataTable(string sql) {
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
cmd.Transaction = tran;
SqlDataAdapter da=new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
/// <summary>
/// 根据SQL语句返回包含结果集的数据表
/// </summary>
/// <param name= "cmd "> 命令对象 </param>
/// <returns> 返回数据表 </returns>
public DataTable GetDataTable(SqlCommand cmd) {
cmd.Connection = conn;
cmd.Transaction = tran;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
/// <summary>
/// 执行UPDATE，DELETE，INSERT类型的SQL语句
/// </summary>
/// <param name= "sql "> SQL语句 </param>
/// <returns> 返回受影响的行数 </returns>
public int update(string sql) {
int rs = 0;
cmd=new SqlCommand(sql,conn);
cmd.Transaction = tran;
rs = cmd.ExecuteNonQuery();
return rs;
}
/// <summary>
/// 执行UPDATE，DELETE，INSERT类型的SQL语句
/// </summary>
/// <param name= "sql "> 命令对象 </param>
/// <returns> 返回受影响的行数 </returns>
public int update(SqlCommand cmd) {
int rs = 0;
cmd.Connection = conn;
cmd.Transaction = tran;
rs = cmd.ExecuteNonQuery();
return rs;
}
/// <summary>
/// 返回结果集中第一行第一个，其他忽略。
/// </summary>
/// <param name= "sql "> SQL语句 </param>
/// <returns> 信息对象 </returns>
public object GetOneInfo(string sql) {
object rs = null;
cmd=new SqlCommand(sql,conn);
cmd.Transaction = tran;
rs = cmd.ExecuteScalar();
return rs;
}
/// <summary>
/// 返回结果集中第一行第一个，其他忽略。
/// </summary>
/// <param name= "sql "> 命令对象 </param>
/// <returns> 信息对象 </returns>
public object GetOneInfo(SqlCommand cmd) {
object rs = null;
cmd.Connection = conn;
cmd.Transaction = tran;
rs = cmd.ExecuteScalar();
return rs;
}
/// <summary>
/// 返回包含结果集的阅读器
/// </summary>
/// <param name= "sql "> SQL语句 </param>
/// <returns> 阅读器对象 </returns>
public SqlDataReader GetDataReader(string sql) {
cmd=new SqlCommand(sql,conn);
cmd.Transaction = tran;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 返回所含结果集的数据集对像
/// </summary>
/// <param name= "sql "> SQL语句 </param>
/// <returns> 数据集对像 </returns>
public DataSet GetDataSet(string sql) {
cmd = new SqlCommand(sql,conn);
cmd.Transaction = tran;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
/// <summary>
/// 返回包含结果集的阅读器
/// </summary>
/// <param name= "cmd "> 命令对象 </param>
/// <returns> 阅读器对象 </returns>
public SqlDataReader GetDataReader(SqlCommand cmd) {
cmd.Connection = conn;
cmd.Transaction = tran;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
[解决办法]
1.可以考虑使用SqlHelper类
[解决办法]
google.search( 'sqlhelper ')
[解决办法]
用参数传递比较好
[解决办法]
同意用SqlHelper试试
[解决办法]
SqlHelper 搞定了
