www.pudn.com > WEBrsgl6.20.rar > DataBase.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataAccess
{
///
///
///
public class DataBase
{
private String Server, dbName;
private String SqlConnectionString;
private SqlConnection con;
public DataBase()
{
// 初始化数据库连接字符串
Server = ConfigurationSettings.AppSettings["Server"].Trim();
dbName = ConfigurationSettings.AppSettings["Database"].Trim();
SqlConnectionString = "user id=sa; initial catalog=" + dbName + ";data source=" + Server + ";Connect Timeout=30";
}
///
/// 打开数据库连接。
///
private void Open()
{
if (con == null)
{
con = new SqlConnection(SqlConnectionString);
con.Open();
}
}
///
/// 关闭数据库连接。
///
public void Close()
{
if (con != null)
{
con.Close();
this.Dispose();
}
}
///
/// Release resources.
/// 释放资源。
///
public void Dispose()
{
// make sure connection is closed
if (con != null)
{
con.Dispose();
con = null;
}
}
///
/// 测试数据库连接是否成功
///
/// server
/// database
/// 用户名
/// 密码
/// bool
public bool TestConnection(String server, String database, String uid, String password)
{
try
{
con = null;
SqlConnectionString = "user id=" + uid + ";password=" + password + ";initial catalog=" + database + ";data source=" + server + ";Connect Timeout=30";
this.Open();
}
catch
{
return false;
}
return true;
}
///
/// 创建command对象以便执行sql语句。
///
/// Sql Text.
/// Command object.
private SqlCommand CreateCommand(string sql)
{
// make sure connection is open
Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
return cmd;
}
///
/// 创建带Prameters的Command对象
///
/// sql语句
/// SqlParameters参数
/// Command对象
public SqlCommand CreateCommand(String sql, SqlParameter[] prams)
{
Open();
SqlCommand cmd = new SqlCommand(sql,con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
return cmd;
}
///
/// 创建执行存储过程的SqlCommand对象。
///
/// 存储过程名
/// 参数
/// SqlCommand对象
public SqlCommand CreateProcedureCommand(String p_upName, SqlParameter[] p_prams)
{
Open();
SqlCommand cmd = new SqlCommand(p_upName, con);
cmd.CommandType = CommandType.StoredProcedure;
if (p_prams != null)
{
foreach (SqlParameter parameter in p_prams)
cmd.Parameters.Add(parameter);
}
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}
///
/// 创建执行存储过程的SqlCommand对象。无参数
///
/// 存储过程名
/// SqlCommand对象
public SqlCommand CreateProcedureCommand(String p_upName)
{
Open();
SqlCommand cmd = new SqlCommand(p_upName, con);
cmd.CommandType = CommandType.StoredProcedure;
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return cmd;
}
///
/// 执行存储过程 eg:无参数
/// RunProc("upProcedureName", prams); // run the stored procedure
/// strVlaue = (string) prams[index].Value; // get the output param value
///
/// 存储过程名
/// 成功是否
public string RunProc(string procName)
{
string a = "ok";
SqlCommand cmd = CreateProcedureCommand(procName);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
a = ex.Message.ToString(); ;
}
finally
{
this.Close();
}
return a;
}
///
/// 执行存储过程 eg:
/// RunProc("upProcedureName", prams); // run the stored procedure
/// strVlaue = (string) prams[index].Value; // get the output param value
///
/// 存储过程名
/// 参数
/// 成功是否
public string RunProc(string procName, SqlParameter[] prams)
{
string a = "ok";
SqlCommand cmd = CreateProcedureCommand(procName, prams);
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
a = ex.Message.ToString();;
}
finally
{
this.Close();
}
return a;
}
///
/// 执行一个无返回的sql语句
///
/// sql语句
/// 执行结果
public bool QueryExec(string sql)
{
SqlCommand cmd = CreateCommand(sql);
//try
//{
cmd.ExecuteNonQuery();
//}
//catch(Exception ex)
//{
// ex.Message.ToString();
// return false;
//}
//finally
//{
// this.Close();
//}
return true;
}
///
/// 执行一个无返回的sql语句，带parameters
///
/// sql语句
/// 执行结果
public bool QueryExec(string sql, SqlParameter[] parms)
{
SqlCommand cmd = CreateCommand(sql, parms);
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
finally
{
this.Close();
}
return true;
}
///
/// 执行一个插入记录操作，返回primary key
///
/// insert sql语句
/// 返回的primary key
public String InsertExec(string sql)
{
sql += ";SELECT @@identity AS [@@IDENTITY];";
SqlCommand cmd = CreateCommand(sql);
try
{
return cmd.ExecuteScalar().ToString();
}
catch
{
return null;
}
finally
{
this.Close();
}
}
///
/// 执行一个插入记录操作，带parameters，返回primary key
///
/// insert sql语句
/// 返回的primary key
public String InsertExec(string sql, SqlParameter[] prams)
{
sql += ";SELECT @@identity AS [@@IDENTITY];";
SqlCommand cmd = CreateCommand(sql, prams);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
///
/// 通过查询指定的SQL语句来获得一个返回值
///
/// SQL语句
/// 返回值
public String QueryValue(string sql)
{
SqlCommand cmd = CreateCommand(sql);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
///
/// 通过查询指定的SQL语句来获得一个返回表，带parms
///
///
///
///
/// 通过查询指定的SQL语句来获得一个返回值，带parms
///
/// SQL语句
/// 返回值
public String QueryValue(string sql, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(sql, prams);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
///
/// 查询返回DATAREADER
///
///
///
///
/// sql语句
public SqlDataReader QueryDataReader(string sql)
{
try
{
SqlCommand cmd = CreateCommand(sql);
return cmd.ExecuteReader();
}
catch
{
return null;
}
finally
{
//
}
}
///
/// 查询返回DATASET
///
/// sql语句
/// DataSet对象
public DataSet QueryDataSet (string sql)
{
SqlCommand cmd = CreateCommand(sql);
DataSet ds = new DataSet();
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(ds);
}
catch (Exception ex)
{
ex.Message.ToString();
ds = null;
}
finally
{
this.Close();
}
return ds;
}
///
/// Query Open Result With DataTable.
/// 通过sql语句返回DataTable。
/// Create by wujq.
///
/// DataTable
public DataTable QueryDataTable(string sql)
{
DataTable dataTable = new DataTable();
SqlCommand cmd = CreateCommand(sql);
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataTable);
}
catch(Exception ex)
{
ex.Message.ToString();
dataTable = null;
}
finally
{
this.Close();
}
return dataTable;
}
///
/// Make input param.
/// 包装输入参数。
///
/// Name of param.
/// Param type.
/// Param size.
/// Param value.
/// New parameter.
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// Make output param.
/// 包装输出参数。
///
/// Name of param.
/// Param type.
/// Param size.
/// New parameter.
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Value);
}
///
/// Make stored procedure param.
/// 包装Command参数。
///
/// Name of param.
/// Param type.
/// Param size.
/// Parm direction.
/// Param value.
/// New parameter.
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if(Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
else Value = param.Value;
return param;
}
///
/// 返回一个表或视图
///
/// 生成表或视图的SQL的语句
/// New View
public DataView View(string sql)
{
DataTable ds = new DataTable();
try
{
SqlCommand com = CreateCommand(sql);
SqlDataAdapter sqladap = new SqlDataAdapter(com);
sqladap.Fill(ds);
}
catch
{
//ds = null;
}
finally
{
this.Close();
}
return ds.DefaultView;
}
}
}