using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Data.SqlTypes ;
using System.Windows.Forms ;
using System.Collections;
namespace Database
{
/// <summary>
/// Database 的摘要说明。
/// </summary>
public class Database
{
/// <summary>
/// 属性
/// </summary>
// public DataSet dataSet
// {
// get
// {
// return m_DataSet;
// }
// }
public Database()
{
//
// TODO: 在此处添加构造函数逻辑
//
XmlRead ConStr = new XmlRead();
if (ConStr.ReadAllConnectNode())
{
constr = ConStr.connstring ;
// try
// {
// Open();
// }
// catch(Exception Ex)
// {
// MessageBox.Show("数据库连接错 误"+Ex.ToString () );
// }
}
else
{
constr = " -1 " ;
// throw new SqlErrorCollection();
}
}
// public bool Open()
// {
// mcn.ConnectionString = constr;
// try
// {
// mcn.Open();
// }
// catch( Exception)
// {
// return false;
// }
// return true;
// }
/// <summary>
/// 默认获取DataSet
/// </summary>
/// <param name="pMyTableName"></param>
/// <param name="tmpMyComputerName"></param>
/// <returns></returns>
// public virtual int getData (string pMyTableName ,string tmpMyComputerName)
// {
// return -1;
// }
#region ExecuteNonQuery
/// <summary>
/// 执行一个SQL Command(使用ConnectString)
/// </summary>
/// <param name="connString"> ConnectString(Sql连接字符串) </param>
/// <param name="cmdType"> Command类型 </param>
/// <param name="cmdText"> Command的语句(SQL语句) </param>
/// <param name="cmdParms"> Command的参数(SqlParameter[]数组类型) </param>
/// <returns> Command 的返回值(受影响的行数) </returns>
public 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;
}
}
/// <summary>
/// 执行一个SQL Command(使用隐含的ConnectString)
/// </summary>
/// <param name="cmdType"> Command类型 </param>
/// <param name="cmdText"> Command的语句(SQL语句) </param>
/// <param name="cmdParms"> Command的参数(SqlParameter[]数组类型) </param>
/// <returns> Command 的返回值(受影响的行数) </returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(constr))
{
PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
// public static int ExecuteNonQuery(string cmdText)
// {
// }
/// <summary>
/// 执行一个简单的查询, 只需要输入SQL语句, 一般用于更新或者删除
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int ExecuteNonQuery( string sqlText)
{
return ExecuteNonQuery(CommandType.Text,sqlText);
}
/// <summary>
/// 执行一个SQL Command(使用SqlTransaction)
/// </summary>
/// <param name="trans"> 使用的SqlTransaction </param>
/// <param name="cmdType"> Command类型 </param>
/// <param name="cmdText"> Command的语句(SQL语句) </param>
/// <param name="cmdParms"> Command的参数(SqlParameter[]数组类型) </param>
/// <returns> Command 的返回值(受影响的行数) </returns>
public 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>
/// 根据指定DsCommandType类型,自动生成cmd执行 dataset的更新
/// </summary>
/// <param name="connString"> ConnectString(Sql连接字符串) </param>
/// <param name="cmdType"> Command类型 </param>
/// <param name="dsCommandType"> Enum类型 </param>
/// <param name="cmdText"> Command的语句(SQL语句) </param>
/// <param name="dataset"> dataset </param>
/// <param name="tablename"> 表名 </param>
/// <param name="cmdParms"> Command的参数(SqlParameter[]数组类型) </param>
/// <returns> 是 否更新成功 </returns>
public bool ExecuteNonQuery( string connString,CommandType cmdType,CommandEnum.DsCommandType dsCommandType, string cmdText,DataSet dataset, string tablename, params SqlParameter[] cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null )
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
switch (dsCommandType)
{
case CommandEnum.DsCommandType.InsertCommand:
dsCommand.InsertCommand = cmd;
break ;
case CommandEnum.DsCommandType.UpdateCommand:
dsCommand.UpdateCommand = cmd;
break ;
case CommandEnum.DsCommandType.DeleteCommand:
dsCommand.DeleteCommand = cmd;
break ;
default : break ;
}
dsCommand.Update(dataset,tablename);
if ( dataset.HasErrors )
{
dataset.Tables[tablename].GetErrors()[ 0 ].ClearErrors();
return false ;
}
else
{
dataset.AcceptChanges();
return true ;
}
}
}
/// <summary>
/// 更新一个记录集(使用connString)
/// </summary>
/// <param name="connString"> ConnectString(Sql连接字符串) </param>
/// <param name="cmdInsertType"> commandInsert类型 </param>
/// <param name="cmdInsertText"> SQL语句(Insert) </param>
/// <param name="cmdUpdateType"> commandUpdate类型 </param>
/// <param name="cmdUpdateText"> SQL语句(Update) </param>
/// <param name="cmdInsertType"> commandDelete类型 </param>
/// <param name="cmdDeleteText"> SQL语句(Delete) </param>
/// <param name="cmdInsertParms"> InsertCommand参数 </param>
/// <param name="cmdUpdateParms"> UpdateCommand参数 </param>
/// <param name="cmdDeleteParms"> DeleteCommand参数 </param>
/// <param name="dataset"> dataset </param>
/// <param name="tablename"> 表名 </param>
/// <returns> 是 否更新成功 </returns>
public bool UpdateDataset( string connString,CommandType cmdInsertType, string cmdInsertText,CommandType cmdUpdateType, string cmdUpdateText,CommandType cmdDeleteType, string cmdDeleteText,SqlParameter[] cmdInsertParms,SqlParameter[] cmdUpdateParms,SqlParameter[] cmdDeleteParms,DataSet dataset, string tablename)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
using (SqlConnection conn = new SqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
if (cmdInsertText != String.Empty)
{
SqlCommand cmdInsert = new SqlCommand();
cmdInsert.Connection = conn;
cmdInsert.CommandText = cmdInsertText;
cmdInsert.CommandType = cmdInsertType;
if (cmdInsertParms != null )
{
foreach (SqlParameter parm in cmdInsertParms)
cmdInsert.Parameters.Add(parm);
}
dsCommand.InsertCommand = cmdInsert;
}
if (cmdUpdateText != String.Empty)
{
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.Connection = conn;
cmdUpdate.CommandText = cmdUpdateText;
cmdUpdate.CommandType = cmdUpdateType;
if (cmdUpdateParms != null )
{
foreach (SqlParameter parm in cmdUpdateParms)
cmdUpdate.Parameters.Add(parm);
}
dsCommand.UpdateCommand = cmdUpdate;
}
if (cmdDeleteText != String.Empty)
{
SqlCommand cmdDelete = new SqlCommand();
cmdDelete.Connection = conn;
cmdDelete.CommandText = cmdDeleteText;
cmdDelete.CommandType = cmdDeleteType;
if (cmdDeleteParms != null )
{
foreach (SqlParameter parm in cmdDeleteParms)
cmdDelete.Parameters.Add(parm);
}
dsCommand.DeleteCommand = cmdDelete;
}
if (cmdInsertText == String.Empty && cmdUpdateText == String.Empty && cmdDeleteText == String.Empty)
{
SqlCommandBuilder scb = new SqlCommandBuilder(dsCommand);
return false ;
}
dsCommand.Update(dataset,tablename);
if ( dataset.HasErrors )
{
dataset.Tables[tablename].GetErrors()[ 0 ].ClearErrors();
return false ;
}
else
{
dataset.AcceptChanges();
return true ;
}
}
}
#endregion
#region ExecuteReader
/// <summary>
/// 获取一个SqlDataReader(使用connString)
/// </summary>
/// <param name="connString"> ConnectString </param>
/// <param name="cmdType"> 类型 </param>
/// <param name="cmdText"> Command的语句(select语句) </param>
/// <param name="cmdParms"> Command的参数 </param>
/// <returns> 所 需要的SqlDataReader </returns>
public SqlDataReader ExecuteReader( string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
try
{
PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw ;
}
}
/// <summary>
/// 获取一个SqlDataReader(使用connString), 使用缺省的 ConnectionString
/// </summary>
/// <param name="cmdType"> 类型 </param>
/// <param name="cmdText"> Command的语句(select语句) </param>
/// <param name="cmdParms"> Command的参数 </param>
/// <returns> SqlDataReader </returns>
public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(constr);
try
{
PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw ;
}
}
/// <summary>
/// 获取一个SqlDataReader, 使用缺省的 ConnectionString
/// </summary>
/// <param name="cmdtxt"> 语句命令 </param>
/// <returns></returns>
public SqlDataReader ExecuteReader( string cmdtxt)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(constr);
try
{
cmd = new SqlCommand(cmdtxt,conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
catch
{
conn.Close();
throw ;
}
}
#endregion
#region private 函数
/// <summary>
/// 准备一个Command(使用SqlParameter[]数组)
/// </summary>
private void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
try
{
conn.Open();
}
catch (Exception Ex)
{
throw Ex;
// string a = Ex.ToString();
// return;
}
}
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);
}
}
private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameterCollection 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);
}
}
/// <summary>
/// 加入一个以字段名为名称的param
/// </summary>
/// <param name="fld"></param>
/// <returns></returns>
private SqlParameter NewFieldParam( string fld)
{
SqlParameter param = new SqlParameter();
param.ParameterName = " @ " + fld;
param.SourceColumn = fld;
return param;
}
/// <summary>
/// 判断字符是否在一个集合中
/// </summary>
/// <param name="str"></param>
/// <param name="ExcludeFields"></param>
/// <returns></returns>
private bool InColleciton( string str,IList ExcludeFields)
{
foreach ( string s in ExcludeFields)
{
if (s.ToUpper() == str.ToUpper())
return true ;
}
return false ;
}
#endregion
#region 填充 DataSet
/// <summary>
/// 将数据填充到DataSet中(无connString)
/// </summary>
/// <param name="cmdType"> 类型 </param>
/// <param name="cmdText"> Command的语句 </param>
/// <param name="tablename"> 表名 </param>
/// <param name="cmdParms"> Command的参数 </param>
public void FillData(CommandType cmdType, string cmdText,DataSet dataset, string tablename, params SqlParameter[] cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
dsCommand.SelectCommand = cmd;
// dsCommand.TableMappings.Add("Table",tablename);
using (SqlConnection conn = new SqlConnection(constr))
{
PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms);
dsCommand.Fill(dataset,tablename);
}
}
/// <summary>
/// 将数据填充到DataSet中(使用 connString + SqlParameterCollection)
/// </summary>
/// <param name="connString"> ConnectString </param>
/// <param name="cmdType"> 类型 </param>
/// <param name="cmdText"> Command的语句 </param>
/// <param name="tablename"> 表名 </param>
/// <param name="cmdParms"> Command的参数(SqlParameterCollection) </param>
public void FillDataEx( string connString, CommandType cmdType, string cmdText,DataSet dataset, string tablename,SqlParameterCollection cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
dsCommand.SelectCommand = cmd;
dsCommand.TableMappings.Add( " Table " ,tablename);
using (SqlConnection conn = new SqlConnection(connString))
{
PrepareCommand(cmd, conn, null , cmdType, cmdText, cmdParms);
dsCommand.Fill(dataset);
}
}
#endregion
internal string constr = null ;
// = "Uid =sa ;Pwd=sa ;Server = Drago;Database =Northwind";
internal SqlConnection mcn = new SqlConnection();
internal DataSet m_DataSet = new System.Data.DataSet() ;
}
}
c# 通用连接数据库类
最新推荐文章于 2022-05-27 22:14:14 发布