protected void btn_Click(object sender, EventArgs e)
{
//判断非空输入
if (isValidate())
{ using (SqlConnection conn = new SqlConnection(strconn))
{
using (SqlCommand cmd = new SqlCommand("inser_msg", conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("MSG_NAME", txtUser.Text);
cmd.Parameters.AddWithValue("MSG_TELEPH", txtTele.Text);
cmd.Parameters.AddWithValue("MSG_CONTENT", txtCont.Text);
cmd.Parameters.AddWithValue("MSG_ADDRESS", txtAddr.Text);
cmd.Parameters.AddWithValue("MSG_TIME", DateTime.Now);
int result = cmd.ExecuteNonQuery();
conn.Close();
string fd = txtUser.Text;
if (result > 0)
{
Response.Write("ok");
txtUser.Text = txtAddr.Text = txtCont.Text = txtTele.Text = ""; }
}
catch (Exception ex)
{
throw ex;
} } } }
}我想在DBHelper.cs类里面写个数据插入的公有方法，求助各位帮我写下
{
//判断非空输入
if (isValidate())
{ using (SqlConnection conn = new SqlConnection(strconn))
{
using (SqlCommand cmd = new SqlCommand("inser_msg", conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("MSG_NAME", txtUser.Text);
cmd.Parameters.AddWithValue("MSG_TELEPH", txtTele.Text);
cmd.Parameters.AddWithValue("MSG_CONTENT", txtCont.Text);
cmd.Parameters.AddWithValue("MSG_ADDRESS", txtAddr.Text);
cmd.Parameters.AddWithValue("MSG_TIME", DateTime.Now);
int result = cmd.ExecuteNonQuery();
conn.Close();
string fd = txtUser.Text;
if (result > 0)
{
Response.Write("ok");
txtUser.Text = txtAddr.Text = txtCont.Text = txtTele.Text = ""; }
}
catch (Exception ex)
{
throw ex;
} } } }
}我想在DBHelper.cs类里面写个数据插入的公有方法，求助各位帮我写下
//////////////////////////////////////////////////////////////
//filename: SqlHelper.cs
//
//author:
//
//date: 2007.10.31
//
//description: 数据持久层
////////////////////////////////////////////////////////////////using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlClient;/// <summary>
/// SqlHelper 的摘要说明。
/// </summary>
public class DBHelper
{
public static readonly string oleconstring = System.Configuration.ConfigurationSettings.AppSettings["constring"];
public DBHelper()
{ }
/// <summary>
/// Sql数据库增、删、改方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回int类型，返回0则操作失败，返回数大于0则操作成功</returns>
public static int ExecuteNonquery(string sql, params SqlParameter[] param)
{
int bFlag = 0;
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql; if (param.Length > 0)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
} try
{
if (con.State == ConnectionState.Closed)
con.Open();
bFlag = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{
con.Close();
}
}
return bFlag;
} /// <summary>
/// Sql数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回DataTable类型</returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param)
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
da.SelectCommand = cmd;
using (SqlConnection con = new SqlConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql; if (param.Length > 0)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
da.Fill(dt);
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return dt;
} /// <summary>
/// Sql数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回Object类型</returns>
public static Object ExecuteObject(string sql, params SqlParameter[] param)
{
SqlDataReader reader = null;
Object obj = null;
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql; if (param.Length > 0)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = reader[0];
}
//while (reader.Read())
//{
// //for (int i = 1; i < reader.FieldCount; i++)
// //{
// // al.Add(reader[i]);
// //}
// foreach (Object obj in reader)
// {
// al.Add(obj);
// }
//}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return obj;
} /// <summary>
/// Ssl数据库验证方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回bool类型</returns>
public static bool Exists(string sql, params SqlParameter[] param)
{
SqlDataReader reader = null;
bool flag = false;
SqlCommand cmd = new SqlCommand();
using (SqlConnection con = new SqlConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql; if (param.Length > 0)
{
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
flag = true;
}
}
catch (Exception ex)
{
flag = false;
string msg = ex.Message;
}
finally
{ }
}
return flag;
}
}虽然有删减……全部太长了贴不上来……但是应该也够用了……
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using PetShop.Utility;namespace PetShop.SQLServerDAL { public abstract class SQLHelper {
public static readonly string CONN_STRING_NON_DTC = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString1"]);
public static readonly string CONN_STRING_DTC_INV = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString2"]);
public static readonly string CONN_STRING_DTC_ORDERS = ConnectionInfo.DecryptDBConnectionString(ConfigurationSettings.AppSettings["SQLConnString3"]);
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connString)) {
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} /**//// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <res>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString); // we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}catch {
conn.Close();
throw;
}
}
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connString)) {
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
} public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
} public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) {
parmCache[cacheKey] = cmdParms;
} /**//// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</returns>
public static SqlParameter[] GetCachedParameters(string cacheKey) {
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms;
}
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 int ExecuteNonQuery(string sql, params SqlParameter[] sqlparameters)
{
bool isTransaction;
int rownum;
try
{
BeginTransaction();
command = new SqlCommand(sql, connection); command.Transaction = transaction;
foreach (SqlParameter parameter in sqlparameters)
{
command.Parameters.Add(parameter);
}
rownum = command.ExecuteNonQuery(); Commit();
this.Close();
return rownum;
}
catch (SqlException sqlex)
{
Rollback();
throw sqlex;
}
catch (Exception ex)
{
Rollback();
throw ex;
}
}
public SqlDataReader ExecuteReader(string sql)
{
SqlDataReader reader;
try
{
Open();
command = new SqlCommand(sql, connection);
reader = command.ExecuteReader();
return reader;
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet ExecuteDataSet(string sql)
{
DataSet ds = new DataSet();
try
{
Open();
command = new SqlCommand(sql, connection);
adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
return ds;
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet ExecuteDataSet(string sql, params SqlParameter[] sqlparameters)
{
DataSet ds = new DataSet();
try
{
Open();
command = new SqlCommand(sql, connection);
foreach (SqlParameter parameter in sqlparameters)
{
command.Parameters.Add(parameter);
}
adapter = new SqlDataAdapter(command); adapter.Fill(ds);
return ds;
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
public string ExecuteScalar(string sql, params SqlParameter[] sqlparameters)
{
string strValue = "";
try
{
Open();
command = new SqlCommand(sql, connection);
foreach (SqlParameter parameter in sqlparameters)
{
command.Parameters.Add(parameter);
}
if (command.ExecuteScalar() != null)
strValue = command.ExecuteScalar().ToString();
return strValue;
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
{
try
{
Open();
Close(); return true;
}
catch
{ return false;
}
}
public void Open()
{
if (connection == null)
{ connectString = System.Configuration.ConfigurationManager.AppSettings["connection"]; connection = new SqlConnection(connectString);
} if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
}
public void Close()
{
if (connection.State == ConnectionState.Open)
connection.Close();
} public void BeginTransaction()
{
if (connection == null || connection.State == ConnectionState.Closed)
this.Open();
transaction = connection.BeginTransaction();
} public void Commit()
{
if (connection != null && connection.State == ConnectionState.Open) transaction.Commit(); this.Close();
} public void Rollback()
{
if (connection != null && connection.State == ConnectionState.Open) transaction.Rollback(); this.Close();
}