using system;
using system.collections.generic;
using system.text;
using system.collections;
using system.data.sqlclient;
using system.data;
using system.configuration;
public class sqlhelper
   {
       //取得数据库连接web.config 中配置 
       public static readonly string connectstring = configurationmanager.connectionstrings[dbstring].connectionstring;
/// <summary>
       /// 无事务，数据查询
       /// </summary>
       /// <param name="cmdtype">存储过程或sql语句</param>
       /// <param name="cmdtext">存储过程名或sql语句内容</param>
       /// <param name="commandparams">参数列表</param>
       /// <returns></returns>
       public static int executenonquery(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
sqlconnection conn = new sqlconnection(connectstring);
try
           {
               preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparams);
               int val = cmd.executenonquery();
               cmd.parameters.clear();
               return val;
           }
           catch
           {
               throw;
           }
           finally
           {
               conn.close();
}
}
/// <summary>
       /// 有事务，数据操作类
       /// </summary>
       /// <param name="trans">事务</param>
       /// <param name="cmdtype">操作类别 (stored procedure,sql)</param>
       /// <param name="cmdtext">存储过程名或sql语句</param>
       /// <param name="commandparams">参数</param>
       /// <returns>返回影响的数据行数</returns>
       public static int executenonquery(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
sqlcommand cmd = new sqlcommand();
           preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, commandparams);
           /*if (cmdtype == commandtype.storedprocedure)
           {
               cmd.parameters.add(@return_value, ).direction = parameterdirection.returnvalue;
               cmd.executenonquery();
val = (int)cmd.parameters[@return_value].value;
           }
           else
               if (cmdtype==commandtype.text)
                {
                  val = cmd.executenonquery();
                 }*/
           int val = cmd.executenonquery();
           cmd.parameters.clear();
return val;
       }
/// <summary>
       /// 返回数据集 datareader
       /// </summary>
       /// <param name="cmdtype"></param>
       /// <param name="cmdtext"></param>
       /// <param name="commandparams"></param>
       /// <returns></returns>
       public static sqldatareader executereader(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
           sqlconnection conn = new sqlconnection(connectstring);
try
           {
               preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparams);
               sqldatareader rdr = cmd.executereader(commandbehavior.closeconnection);
               cmd.parameters.clear();
               return rdr;
           }
           catch (exception ex)
           {
               conn.close();
               //   throw new exception(操作失败！);
               throw new exception(ex.message);
           }
       }
/// <summary>
       /// 有事务的取数据
       /// </summary>
       /// <param name="trans"></param>
       /// <param name="cmdtype"></param>
       /// <param name="cmdtext"></param>
       /// <param name="commandparams"></param>
       /// <returns></returns>
       public static sqldatareader executereader(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
           preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, commandparams);
           sqldatareader rdr = cmd.executereader(commandbehavior.closeconnection);
           cmd.parameters.clear();
           return rdr;
       }
public static object executescalar(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
sqlconnection conn = new sqlconnection(connectstring);
try
           {
               preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparams);
               object val = cmd.executescalar();
               cmd.parameters.clear();
               return val;
           }
           catch
           {
               throw;
           }
           finally
           {
               conn.close();
           }
}
public static object executescalar(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
           preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, commandparams);
           object val = cmd.executescalar();
           cmd.parameters.clear();
           return val;
}
/// <summary>
       /// 根据sql语句取得表
       /// </summary>
       /// <param name="cmdtype"></param>
       /// <param name="cmdtext"></param>
       /// <param name="commandparams"></param>
       /// <returns></returns>
       public static datatable executetable(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           datatable temptable = new datatable();
sqlcommand cmd = new sqlcommand();
sqlconnection conn = new sqlconnection(connectstring);
try
           {
               preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparams);
sqldataadapter da = new sqldataadapter(cmd);
sqlcommandbuilder scb = new sqlcommandbuilder(da);
da.fill(temptable);
           }
           finally
           {
               conn.close();
           }
return temptable;
       }
public static datatable executetable(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           datatable temptable = new datatable();
           sqlcommand cmd = new sqlcommand();
preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, commandparams);
           sqldataadapter da = new sqldataadapter(cmd);
           sqlcommandbuilder scb = new sqlcommandbuilder(da);
           da.fill(temptable);
           cmd.parameters.clear();
return temptable;
}
/// <summary>
       /// 根据sql语句或存储过程取得数据
       /// </summary>
       /// <param name="cmdtype"></param>
       /// <param name="cmdtext"></param>
       /// <param name="commandparams"></param>
       /// <returns></returns>
       public static dataset executedataset(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlconnection conn = new sqlconnection(connectstring);
sqlcommand cmd = new sqlcommand();
dataset tempdataset = new dataset();
try
           {
               preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparams);
               sqldataadapter sqladapter = new sqldataadapter(cmd);
               sqladapter.fill(tempdataset);
               cmd.parameters.clear();
               return tempdataset;
}
           finally
           {
               conn.close();
           }
}
public static dataset executedataset(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] commandparams)
       {
           sqlcommand cmd = new sqlcommand();
           dataset tempdataset = new dataset();
           preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, commandparams);
           sqldataadapter sqladapter = new sqldataadapter(cmd);
           sqladapter.fill(tempdataset);
           cmd.parameters.clear();
           return tempdataset;
       }
/// <summary>
       /// 生成sql语句或准备
       /// </summary>
       /// <param name="cmd"></param>
       /// <param name="conn"></param>
       /// <param name="trans"></param>
       /// <param name="cmdtype"></param>
       /// <param name="cmdtext"></param>
       /// <param name="cmdparms"></param>
       private static void preparecommand(sqlcommand cmd, sqlconnection conn, sqltransaction trans, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
       {
if (conn.state != connectionstate.open)
               conn.open();
cmd.connection = conn;
           cmd.commandtext = cmdtext;
if (trans != null)
               cmd.transaction = trans;
cmd.commandtype = cmdtype;
if (cmdparms != null)
           {
               foreach (sqlparameter parm in cmdparms)
                   cmd.parameters.add(parm);
           }
       }
public static object todbvalue(object value)
       {
           return value == null ? dbnull.value : value;
       }
public static object fromdbvalue(object dbvalue)
       {
           return dbvalue == dbnull.value ? null : dbvalue;
       }
   }
以上就是关于.net c# sql数据库sqlhelper类实例代码的详细内容。