using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
namespace DBclass
... {
/**//// <summary>
/// DBClass 的摘要说明。
/// </summary>
public class DBclass
...{
//protected static string strConn = ConfigurationSettings.AppSettings["strConnection"];
protected static string strConn=ConfigurationSettings.AppSettings["Asp600.ConnectionString"];
protected static SqlConnection myConn=new SqlConnection(strConn);
protected static string mySql;
private TextWriter Debug_Output;
"***** Debug Setting *****"#region "***** Debug Setting *****"
//判断是否处于调试状态
private bool JudgeDebug()
...{
bool IsDebug=false;
string DebugStr=ConfigurationSettings.AppSettings["Asp600.Debug"];
IsDebug=((DebugStr!=null && DebugStr.Equals("true"))?true:false);
/**//*
if (DebugStr == null && DebugStr.Equals("true"))
{
IsDebug = false;
}
else
{
IsDebug = true;
}
*/
return IsDebug;
}
//信息输出
private void debug(object Debug_Infor)
...{
if(Debug_Output!=null)
...{
Debug_Output.Write(Debug_Infor);
Debug_Output.Write("<br>");
}
}
//启动Debug
public void OpenDebug(TextWriter Do_Output)
...{
if(JudgeDebug())
this.Debug_Output=Do_Output;
}
#endregion
"***** Functions of DataClass *****"#region "***** Functions of DataClass *****"
/**//// <summary>
/// executing SQL commands
/// </summary>
///执行insert delete update语句
/// <param name="mySql">string</param>
/// <returns>return int</returns>
public int ExecuteSql(string mySql)
...{
debug("现在执行的是DBClass中的ExecuteSql(string)方法,返回类型为:int ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
try
...{
debug("执行语句为:"+mySql);
myConn.Open();
return myCmd.ExecuteNonQuery();
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
//throw ex;
return -99;
}
finally
...{
myCmd.Dispose();
myConn.Close();
}
}
//执行SqlCommand
public int ExecuteSql(SqlCommand scmd)
...{
debug("现在执行的是DBClass中的ExecuteSql(SqlCommand)方法,返回类型为:int ");
scmd.Connection=myConn;
try
...{
debug("执行语句为:"+scmd.CommandText.ToString());
myConn.Open();
return scmd.ExecuteNonQuery();
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
//throw ex;
return -99;
}
finally
...{
scmd.Dispose();
myConn.Close();
}
}
/**//// <summary>
///executing SQL commands查询返回植是一个植
/// </summary>
/// <param name="mySql">要执行的SQL语句,为字符串类型string</param>
/// <returns>返回执行情况,整形int</returns>
public int ExecScalar(string mySql)
...{
debug("现在执行的是DBClass中的ExecScalar(mySql)方法,返回类型为:int ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
try
...{
debug("执行语句为:"+mySql);
myConn.Open();
SqlDataReader myDr = myCmd.ExecuteReader();
if(myDr.Read())
...{
return 1;
}
else
...{
return 0;
}
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
return -99;
//throw ex;
}
finally
...{
myCmd.Dispose();
myConn.Close();
}
}
//执行SqlCommand
public int ExecScalar(SqlCommand scmd)
...{
debug("现在执行的是DBClass中的ExecScalar(SqlCommand)方法,返回类型为:int ");
scmd.Connection=myConn;
try
...{
debug("执行语句为:"+scmd.CommandText.ToString());
myConn.Open();
SqlDataReader myDr = scmd.ExecuteReader();
if(myDr.Read())
...{
return 1;
}
else
...{
return 0;
}
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
//throw ex;
return -99;
}
finally
...{
scmd.Dispose();
myConn.Close();
}
}
public string str(string str)
...{
string result="'"+str.Replace("'",""")+"'";
return result;
}
public string sear(string str)
...{
string result=str.Replace("'",""");
return result;
}
/**//// <summary>
/// get dataset
/// </summary>执行select语句返回dataset
/// <param name="mySql">(string)</param>
/// <returns>(DataSet)</returns>
public DataSet ExecuteSqlDs(string mySql ,string myTable)
...{
debug("现在执行的是DBClass中的ExecuteSqlDs(string,string)方法,返回类型为:DataSet ");
SqlDataAdapter myDa =new SqlDataAdapter(mySql, myConn);
DataSet myDs=new DataSet();
try
...{
debug("执行语句为:"+mySql);
myConn.Open();
myDa.Fill(myDs, myTable);
return myDs;
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
return new DataSet();
//throw ex;
}
finally
...{
myDa.Dispose();
myConn.Close();
}
}
public DataSet ExecuteSqlDs(SqlCommand scmd,string myTable)
...{
debug("现在执行的是DBClass中的ExecuteSqlDs(SqlCommand,string)方法,参数类型为SqlCommand,返回类型为:DataSet ");
scmd.Connection=myConn;
SqlDataAdapter myDa =new SqlDataAdapter();
myDa.SelectCommand=scmd;
DataSet ds=new DataSet();
try
...{
debug("执行语句为:"+scmd.CommandText.ToString());
myConn.Open();
myDa.Fill(ds,myTable);
return ds;
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
return new DataSet();
}
finally
...{
//myCmd.Dispose();
myConn.Close();
}
}
//定义分页
public DataSet ExecuteSqlDsReapter(string mySql ,int repeatestr1,int repeatestr2,string myTable)
...{
debug("现在执行的是DBClass中的ExecuteSqlDsReapter(string,int,int,string)方法,返回类型为:DataSet ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
DataSet dsReapter = new DataSet();
try
...{
//myConn.Open();
debug("执行语句为:"+mySql);
myDa.Fill(dsReapter,repeatestr1,repeatestr2,myTable);
return dsReapter;
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
return new DataSet();
//throw ex;
}
finally
...{
myDa.Dispose();
//myConn.Close();
}
}
//定义嵌套循环
public DataSet ExecuteSqlChannelReapter(string mySql1 ,string mySql2 ,string myTable1,string myTable2)
...{
SqlCommand myCmd1 = new SqlCommand(mySql1,myConn);
SqlCommand myCmd2 = new SqlCommand(mySql2,myConn);
SqlDataAdapter myDa1 = new SqlDataAdapter(myCmd1);
SqlDataAdapter myDa2 = new SqlDataAdapter(myCmd2);
DataSet dsReapter = new DataSet();
try
...{
//myConn.Open();
myDa1.Fill(dsReapter,myTable1);
myDa2.Fill(dsReapter,myTable2);
return dsReapter;
}
catch(Exception ex)
...{
throw ex;
}
finally
...{
myDa1.Dispose();
myDa2.Dispose();
//myConn.Close();
}
}
/**//// <summary>
/// datareader
/// </summary>
/// <param name="mySql">string</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader ExecuteSqlDataReader(string mySql)
...{
debug("现在执行的是DBClass中的ExecuteSqlDataReader(string)方法,返回类型为:SqlDataReader ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
SqlDataReader myReader;
try
...{
debug("执行语句为:"+mySql);
myConn.Open();
myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString()); ;
return null;
//throw ex;
}
finally
...{
myCmd.Dispose();
//myConn.Close();
}
}
/**//// <summary>
/// get single value
/// </summary>
/// <param name="mySql">(string)</param>
/// <returns>(int)</returns>
public int ExecuteCount(string mySql)
...{
debug("现在执行的是DBClass中的ExecuteCount(string)方法,返回类型为:int ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
myCmd.CommandText = mySql;
try
...{
debug("执行语句为:"+mySql);
myConn.Open();
return (int)myCmd.ExecuteScalar();
}
catch(Exception ex)
...{
debug("异常信息为:"+ex.ToString());
return -99;
//throw ex;
}
finally
...{
myCmd.Dispose();
myConn.Close();
}
}
/**//// <summary>
/// get object
/// </summary>
/// <param name="mySql">(string)</param>
/// <returns>(object)</returns>
public object ExecuteSql4ValueEx(string mySql)
...{
SqlCommand myCmd = new SqlCommand(mySql,myConn);
try
...{
myConn.Open();
object r = myCmd.ExecuteScalar();
if(Object.Equals(r,null))
...{
throw new Exception("object unavailable!");
}
else
...{
return r;
}
}
catch(Exception ex)
...{
throw ex;
}
finally
...{
myCmd.Dispose();
myConn.Close();
}
}
#endregion
"***** Functions of Procedure *****"#region "***** Functions of Procedure *****"
public SqlCommand BuildIntCommand(string ProcName, IDataParameter[] parameters)
...{
SqlCommand myCmd = BuildQueryCommand(ProcName, parameters );
myCmd.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /**//* Size */
ParameterDirection.ReturnValue,
false, /**//* is nullable */
0, /**//* byte precision */
0, /**//* byte scale */
string.Empty,
DataRowVersion.Default,
null ));
return myCmd;
}
public SqlCommand BuildQueryCommand(string ProcName, IDataParameter[] parameters)
...{
SqlCommand myCmd = new SqlCommand(ProcName,myConn);
myCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
...{
myCmd.Parameters.Add( parameter );
}
return myCmd;
}
public int RunProcedure(string ProcName, IDataParameter[] parameters, out int rowsAffected )
...{
int result;
myConn.Open();
SqlCommand myCmd = BuildIntCommand(ProcName, parameters );
rowsAffected = myCmd.ExecuteNonQuery();
result = (int)myCmd.Parameters["ReturnValue"].Value;
myConn.Close();
return result;
}
public SqlDataReader RunProcedure(string ProcName, IDataParameter[] parameters )
...{
SqlDataReader myReader;
myConn.Open();
SqlCommand myCmd = BuildQueryCommand(ProcName, parameters );
myCmd.CommandType = CommandType.StoredProcedure;
myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
public DataSet RunProcedure(string ProcName, IDataParameter[] parameters, string tableName )
...{
DataSet myDs = new DataSet();
SqlDataAdapter myDa = new SqlDataAdapter();
myDa.SelectCommand = BuildQueryCommand(ProcName, parameters );
myDa.Fill(myDs, tableName );
myConn.Close();
return myDs;
}
public void RunProcedure(string ProcName, IDataParameter[] parameters, DataSet myDs, string tableName )
...{
myConn.Open();
SqlDataAdapter myDa = new SqlDataAdapter();
myDa.SelectCommand = BuildIntCommand(ProcName, parameters );
myDa.Fill(myDs, tableName );
myConn.Close();
}
#endregion
}
}
比较下列代码为相同的东西:
********************(1)*****************
/// <summary>
/// 执行Transact-SQL语句,对数据库记录做插入,修改,删除等操作
/// </summary>
/// <param name="sSQL">Transact-SQL语句</param>
public void ExeSql(string sSQL)
{
ConnOpen();
SqlCommand1 = new SqlCommand(sSQL, SqlConnection1);
SqlCommand1.ExecuteNonQuery();
SqlCommand1.Dispose();
}
*********************************************
--------------------------(2)--------------------------
/// <summary>
/// executing SQL commands
/// </summary>
///执行insert delete update语句
/// <param name="mySql">string</param>
/// <returns>return int</returns>
public int ExecuteSql(string mySql)
{
debug("现在执行的是DBClass中的ExecuteSql(string)方法,返回类型为:int ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
try
{
debug("执行语句为:"+mySql);
myConn.Open();
return myCmd.ExecuteNonQuery();
}
catch(Exception ex)
{
debug("异常信息为:"+ex.ToString());
//throw ex;
return -99;
}
finally
{
myCmd.Dispose();
myConn.Close();
}
}
------------------------------------------------
***************************(3)*********************
/// <param name="mySql">要执行的SQL语句,为字符串类型string</param>
/// <returns>返回执行情况,整形int</returns>
public int ExecScalar(string mySql)
{
debug("现在执行的是DBClass中的ExecScalar(mySql)方法,返回类型为:int ");
SqlCommand myCmd = new SqlCommand(mySql,myConn);
try
{
debug("执行语句为:"+mySql);
myConn.Open();
SqlDataReader myDr = myCmd.ExecuteReader();
if(myDr.Read())
{
return 1;
}
else
{
return 0;
}
}
catch(Exception ex)
{
debug("异常信息为:"+ex.ToString());
return -99;
//throw ex;
}
finally
{
myCmd.Dispose();
myConn.Close();
}
}
************************************************
-----------------------------------(4)------------------------------------
public int ExecScalar(string mySql)
{
myConn.Open();
SqlCommand myCom = new SqlCommand(mySql,myConn);
myCom.CommandType = CommandType.Text;
SqlDataReader myDr = myCom.ExecuteReader();
if (myDr.Read())
{
return 1;
}
else
{
return 0;
}
myDr.Close();
myCom.Dispose();
myConn.Close();
}
-----------------------------------------------