解决方案 »
- POP3 没法读取垃圾邮件中的邮件?
- WCF发布WebService可以只生成一份wsdl吗? 不要import其他schema
- activex问题
- c# serialport 怎么发送汉字?
- 请问net2003有没有支持json的开源类库?
- C#反射机制
- 如何通过姓名的简拼对信息进行检索
- 团队开发下,如何做数据库的维护呢
- 编译好的WEB应用在win2000 ntfs分区,不能够运行,请求帮助 急!急!急!
- Guid从Sql Server导入到Access后的大问题!!
- 不允许更改“ConnectionString”属性。连接的当前状态为打开
- .net c# adpater.update 批量 插入 到 oracle
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//上面是导入命名空间namespace Nova_DAL
{
public class DbHelper
{
HttpCookie Nova_DBNameCookie = HttpContext.Current.Request.Cookies["DBName"]; //通过Cookies获取值
private static string dbProviderName = "System.Data.SqlClient";
protected static string dbConnectionString = "";
private DbConnection connection;
//定义参数
//构造函数
public DbHelper()
{
// 判断这些值是否为空
if (Nova_DBNameCookie == null || Nova_DBNameCookie != null && Nova_DBNameCookie.Value.ToString() == "")
{
//HttpContext.Current.Response.Write("<Script language=javascript>");
//HttpContext.Current.Response.Write("alert('没有配置您公司数据库,请联系**公司!');");
//HttpContext.Current.Response.Write("window.location='login.aspx';");
//HttpContext.Current.Response.Write("</script>");
//return;
//数据库连接词
dbConnectionString = "Data Source=19*.***.1.50;Initial Catalog=DB_WMS_CFG;User ID=sa;Password=asdfgh;Persist Security Info=True;";
}
else
{
//数据库连接词
dbConnectionString = "Data Source=192.**8.*.**;Initial Catalog=" + Nova_DBNameCookie.Value + ";User ID=sa;Password=asdfgh;Persist Security Info=True;";
}
//调用CreateConnection方法
this.connection = CreateConnection(DbHelper.dbConnectionString);
} //构造函数
public DbHelper(string connectionString)
{
this.connection = CreateConnection(connectionString);
}
//创建Sql连接
public static DbConnection CreateConnection()
{
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = DbHelper.dbConnectionString;
return dbconn;
}
//创建Sql连接
public static DbConnection CreateConnection(string connectionString)
{
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbConnection dbconn = dbfactory.CreateConnection();
dbconn.ConnectionString = connectionString;
return dbconn;
}
//执行储存过程
public DbCommand GetStoredProcCommond(string storedProcedure)
{
DbCommand dbCommand = connection.CreateCommand();
dbCommand.CommandText = storedProcedure;
dbCommand.CommandType = CommandType.StoredProcedure;
return dbCommand;
}
//执行简单的SQL语句
public DbCommand GetSqlStringCommond(string sqlQuery)
{
DbCommand dbCommand = connection.CreateCommand();
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = CommandType.Text;
return dbCommand;
} #region 增加参数
//添加参数
public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
{
foreach (DbParameter dbParameter in dbParameterCollection)
{
cmd.Parameters.Add(dbParameter);
}
}
//添加参数
public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Size = size;
dbParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
//添加参数
public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
//添加参数
public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
public DbParameter GetParameter(DbCommand cmd, string parameterName)
{
return cmd.Parameters[parameterName];
} #endregion #region 执行
//执行一个command对象,返回dataset
public DataSet ExecuteDataSet(DbCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
dbDataAdapter.Fill(ds);
return ds;
}
//执行一个command对象,返回datatable
public DataTable ExecuteDataTable(DbCommand cmd)
{
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
dbDataAdapter.Fill(dataTable);
return dataTable;
}
//执行一个command对象,返回datareader对象
public DbDataReader ExecuteReader(DbCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
//执行一个command对象,返回影响的行数
public int ExecuteNonQuery(DbCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
//执行一个command对象,返回第一行第一列的值
public object ExecuteScalar(DbCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
object ret = cmd.ExecuteScalar();
cmd.Connection.Close();
return ret;
}
#endregion #region 执行事务
//执行一个事务对象,返回dataset
public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
{
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
dbDataAdapter.Fill(ds);
return ds;
}
//执行一个事务对象,返回datatable
public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
{
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
dbDataAdapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
dbDataAdapter.Fill(dataTable);
return dataTable;
}
//执行一个事务对象,返回datareader
public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
{
cmd.Connection.Close();
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
DbDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
return reader;
}
public int ExecuteNonQuery(DbCommand cmd, Trans t)
{
cmd.Connection.Close();
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
int ret = cmd.ExecuteNonQuery();
return ret;
}
//执行一个事务对象,返回第一行第一列
public object ExecuteScalar(DbCommand cmd, Trans t)
{
cmd.Connection.Close();
cmd.Connection = t.DbConnection;
cmd.Transaction = t.DbTrans;
object ret = cmd.ExecuteScalar();
return ret;
}
#endregion
}
}