大家看看这样写有什么不好,怎么去改正,参与有分 public class DB
{
private static readonly string Sql = "server=127.0.0.1;uid=sa;pwd=0910;database=SFL_Property";
private SqlConnection conn = null;
private void DBconn()
{
conn = new SqlConnection(Sql);
conn.Open();
}
public SqlDataReader ReturnReader(string str)
{
DBconn();
try
{
SqlCommand myCmd = new SqlCommand(str, conn);
SqlDataReader dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally {
}
}
public void ExeCutSQL(string str) {
DBconn();
try
{
SqlCommand myCmd = new SqlCommand(str, conn);
myCmd.ExecuteNonQuery(CommandBehavior.CloseConnection);
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally {
conn.Close();
}
}
public DataSet ReturnDataSet(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
public DataTable ReturnDataTable(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0];
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
public DataRow ReturnDataRow(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0].Rows[0];
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
}
{
private static readonly string Sql = "server=127.0.0.1;uid=sa;pwd=0910;database=SFL_Property";
private SqlConnection conn = null;
private void DBconn()
{
conn = new SqlConnection(Sql);
conn.Open();
}
public SqlDataReader ReturnReader(string str)
{
DBconn();
try
{
SqlCommand myCmd = new SqlCommand(str, conn);
SqlDataReader dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally {
}
}
public void ExeCutSQL(string str) {
DBconn();
try
{
SqlCommand myCmd = new SqlCommand(str, conn);
myCmd.ExecuteNonQuery(CommandBehavior.CloseConnection);
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally {
conn.Close();
}
}
public DataSet ReturnDataSet(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
public DataTable ReturnDataTable(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0];
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
public DataRow ReturnDataRow(string str) {
DBconn();
try
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0].Rows[0];
}
catch (SqlException e)
{
throw new Exception(e.Message, e);
}
finally
{
conn.Close();
}
}
}
private void DBconn()
{
conn = new SqlConnection(Sql);
conn.Open();
}这个 有点问题
比如 静态的 DataSet 根本就不用 conn.Open();
而且LZ 是传SQL 语句 进去执行 不太喜欢这种操作方式
而非 喜欢不依赖SQL的方式
而且连使用存储过程的机会我都没有看到哦
有点多余
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace db
{
/// <summary>
/// 类,用于数据访问的类。
/// </summary>
public class DB: IDisposable
{
/// <summary>
/// 保护变量,数据库连接。
/// </summary>
protected SqlConnection Connection; /// <summary>
/// 保护变量,数据库连接串。
/// </summary>
protected String ConnectionString;
/// <summary>
/// 构造函数。
/// </summary>
/// <param name="DatabaseConnectionString">数据库连接串</param>
public DB()
{
ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"];
} /// <summary>
/// 构造函数。
/// </summary>
/// <param name="pDatabaseConnectionString">数据库连接串</param>
public DB(string pDatabaseConnectionString)
{
ConnectionString = pDatabaseConnectionString;
} /// <summary>
/// 析构函数,释放非托管资源
/// </summary>
~Database()
{
try
{
if (Connection != null)
Connection.Close();
}
catch{}
try
{
Dispose();
}
catch{}
} /// <summary>
/// 保护方法,打开数据库连接。
/// </summary>
protected void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
} /// <summary>
/// 公有方法,关闭数据库连接。
/// </summary>
public void Close()
{
if (Connection != null)
Connection.Close();
} /// <summary>
/// 公有方法,释放资源。
/// </summary>
public void Dispose()
{
// 确保连接被关闭
if (Connection != null)
{
Connection.Dispose();
Connection = null;
}
}
/// <summary>
/// 公有方法,获取数据,返回一个SqlDataReader (调用后主意调用SqlDataReader.Close())。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader GetDataReader(String SqlString)
{
Open();
SqlCommand cmd = new SqlCommand(SqlString,Connection);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} /// <summary>
/// 公有方法,获取数据,返回一个DataSet。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(String SqlString)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
} /// <summary>
/// 公有方法,获取数据,返回一个DataRow。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>DataRow</returns>
public DataRow GetDataRow(String SqlString)
{
DataSet dataset = GetDataSet(SqlString);
dataset.CaseSensitive = false;
if (dataset.Tables[0].Rows.Count>0)
{
return dataset.Tables[0].Rows[0];
}
else
{
return null;
}
} /// <summary>
/// 公有方法,执行Sql语句。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>对Update、Insert、Delete为影响到的行数,其他情况为-1</returns>
public int ExecuteSQL(String SqlString)
{
int count = -1;
Open();
try
{
SqlCommand cmd = new SqlCommand(SqlString,Connection);
count = cmd.ExecuteNonQuery();
}
catch
{
count = -1;
}
finally
{
Close();
}
return count;
} /// <summary>
/// 公有方法,执行一组Sql语句。
/// </summary>
/// <param name="SqlStrings">Sql语句组</param>
/// <returns>是否成功</returns>
public bool ExecuteSQL(ArrayList SqlStrings)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (String str in SqlStrings)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
} /// <summary>
/// 公有方法,在一个数据表中插入一条记录。
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Cols">哈西表,键值为字段名,值为字段值</param>
/// <returns>是否成功</returns>
public bool Insert(String TableName,Hashtable Cols)
{
int Count = 0; if (Cols.Count<=0)
{
return true;
} String Fields = " (";
String Values = " Values(";
foreach(DictionaryEntry item in Cols)
{
if (Count!=0)
{
Fields += ",";
Values += ",";
}
Fields += item.Key.ToString();
Values += item.Value.ToString();
Count ++;
}
Fields += ")";
Values += ")"; String SqlString = "Insert into "+TableName+Fields+Values; return Convert.ToBoolean(ExecuteSQL(SqlString));
} /// <summary>
/// 公有方法,更新一个数据表。
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Cols">哈西表,键值为字段名,值为字段值</param>
/// <param name="Where">Where子句</param>
/// <returns>是否成功</returns>
public bool Update(String TableName,Hashtable Cols,String Where)
{
int Count = 0;
if (Cols.Count<=0)
{
return true;
}
String Fields = " ";
foreach(DictionaryEntry item in Cols)
{
if (Count!=0)
{
Fields += ",";
}
Fields += item.Key.ToString();
Fields += "=";
Fields += item.Value.ToString();
Count ++;
}
Fields += " "; String SqlString = "Update "+TableName+" Set "+Fields+Where; return Convert.ToBoolean(ExecuteSQL(SqlString));
}
}
}
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace LTP.SQLServerDAL
{
/// <SUMMARY>
/// ADO.NET数据库操作基础类。
/// </SUMMARY>
public abstract class DbManagerSQL
{
//数据库连接字符串
protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
public DbManagerSQL()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <SUMMARY>
/// 执行SQL语句,返回影响的记录数
/// </SUMMARY>
/// <PARAM name="SQLString"></PARAM>
/// <RETURNS></RETURNS>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString,connection))
{
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <SUMMARY>
/// 执行两条SQL语句,实现数据库事务。
/// </SUMMARY>
/// <PARAM name="SQLString1"></PARAM>
/// <PARAM name="SQLString2"></PARAM>
public static void ExecuteSqlTran(string SQLString1,string SQLString2)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();//打开连接
SqlCommand cmd = new SqlCommand();
cmd.Connection=connection; //指定连接对象
SqlTransaction tx=connection.BeginTransaction();//创建一个事务对象
cmd.Transaction=tx; //指定事务对象
try
{
cmd.CommandText=SQLString1;
cmd.ExecuteNonQuery();
cmd.CommandText=SQLString2;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch(System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <SUMMARY>
/// 执行多条SQL语句,实现数据库事务,每条语句以“;”分割。
/// </SUMMARY>
/// <PARAM name="SQLStringList"></PARAM>
public static void ExecuteSqlTran(string SQLStringList)
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
conn.Open();
OdbcCommand cmd = new OdbcCommand();
cmd.Connection=conn;
OdbcTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;
try
{
string [] split= SQLStringList.Split(new Char [] { ';'});
foreach (string strsql in split)
{
if (strsql.Trim()!="")
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch(System.Data.Odbc.OdbcException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <SUMMARY>
/// 执行带一个存储过程参数的的SQL语句。
/// </SUMMARY>
/// <PARAM name="SQLString"></PARAM>
/// <PARAM name="content"></PARAM>
/// <RETURNS></RETURNS>
public static int ExecuteSql(string SQLString,string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString,connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
myParameter.Value = content ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// 向数据库里插入图像格式的字段
/// </SUMMARY>
/// <PARAM name="strSQL"></PARAM>
/// <PARAM name="fs"></PARAM>
/// <RETURNS></RETURNS>
public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
myParameter.Value = fs ;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch(System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <SUMMARY>
/// 执行一条计算查询结果语句,返回查询结果(整数)。
/// </SUMMARY>
/// <PARAM name="strSQL"></PARAM>
/// <RETURNS></RETURNS>
public static int GetCount(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
try
{
connection.Open();
SqlDataReader result = cmd.ExecuteReader();
int i=0;
while(result.Read())
{
i=result.GetInt32(0);
}
result.Close();
return i;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <SUMMARY>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </SUMMARY>
/// <PARAM name="SQLString"></PARAM>
/// <RETURNS></RETURNS>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString,connection);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <SUMMARY>
/// 执行查询语句,返回SqlDataReader
/// </SUMMARY>
/// <PARAM name="strSQL"></PARAM>
/// <RETURNS></RETURNS>
public static SqlDataReader ExecuteReader(string strSQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL,connection);
SqlDataReader myReader;
try
{
connection.Open();
myReader = cmd.ExecuteReader();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <SUMMARY>
/// 执行查询语句,返回DataSet
/// </SUMMARY>
/// <PARAM name="SQLString"></PARAM>
/// <RETURNS></RETURNS>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
}
catch(System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
} }
#region 存储过程操作
/// <SUMMARY>
/// 运行存储过程
/// </SUMMARY>
/// <PARAM name="storedProcName"></PARAM>
/// <PARAM name="parameters"></PARAM>
/// <RETURNS></RETURNS>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, connection );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
connection.Close();
return dataSet;
}
}
#endregion
}
}
public SqlDataReader ReturnReader(string str)
{
DBconn();
try
{
SqlCommand myCmd = new SqlCommand(str, conn);
SqlDataReader dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally {
//应该写上关闭Connection的语句,原因是有异常的情况要考虑
}
}
CommandBehavior.CloseConnection 没意义
第二,未提供執行存儲過程
第三,未提供事務處理
第四,對以讀取 DataReader,最好還是原樣封裝 Open 和 Close 方法,如
myCmd.Open();
myCmd.GetDataReader();
myCmd.Close();
要不然 80% 的人會忘了 Close,最好做 Dispose,可以用 using 語句:)
参考下sqlhelper
另外考虑如果SQL语句使用参数查询怎么办?
就像9楼说的,没有CLOSE一直会占着座位。
后面继续写的会出现错误啊
{
throw new Exception(e.Message);
}
我感觉你在处理Exception的时候有一块“心病”。你不知道Exception有InnerException属性么?在调试、或者.net自己的“异常界面”上显示的异常信息通常都是多个异常的堆栈列表,而不是仅仅显示顶层的一场信息。如果你自己显示异常Message,你会去仅仅显示e.Message?那么你没有一个好的pm对产品做过基本的把关。就算是要显示异常的message,也应该这样写: string GetMessage(Exception e)
{
string mess = string.Empty;
while (e != null)
{
mess += e.Message;
e = e.InnerException;
}
return mess;
}
对于Stack的显示也是如此,是要在Excetion上有点深度地搜索的。而你的那个代码,在Exception正确的层次中脱去表明数据库层异常,毫无必要也减少了Exception中的信息量。删除掉所有的catch (SqlException e)
{
throw new Exception(e.Message);
}之后,你的整个try...catch...finally就显得更加多余,using{}本来就是编译为一层try...catch...finally....处理,你在里边又多谢了一层。最后你的所有try...catch...finally....看上去都是多余的。
我的意思是说,using{}可以取代try...catch...finally....而且不可能丢三落四地忘记finally中的代码。
呵,一時還眞找不着代碼,說一下大體的思路,以 Sql Server 爲例。
對於第一點,可以將參數封裝爲一個 Dictionary<string, object>,然後在調用類似於 GetDataTable() 的方法時,將SQL參數作爲第二個參數傳遞,如 SqlParams.Add(@ID, 5);
myCmd.GetDataTable("Select * From Table Where ID = @ID", SqlParams); 在 GetDataTable 方法的內部 //通過 foreach 獲取 SqlParams 的名值對
foreach(KeyValuePair<string, string> kvp in SqlParams)
{
cmd.Parameters.Add(kvp.Key, kvp.Value.GetType()到SqlDbType的映射).value = kvp.Value;
}
//執行你的 SQL 語句
第二點很簡單,設置 cmd.CommandType = CommandType.StoredProcedure 就可以了第三點事務處理 SqlTransaction tran = null;
try
{
...
tran = conn.BeginTransaction(); //事務開始
cmd.ExecuteNonQuery();
tran.Commit(); //提交事務
}
catch(SqlException ex)
{
tran.Rollback(); //出現異常,囬滾
}
finally
{
//釋放資源
}
第四點,要繼承 IDispose 接口,4樓有現成的代碼,呵呵
myCmd.ExecuteNonQuery(CommandBehavior.CloseConnection);//这个能编译通过?ExecuteNonQuery方法哪有参数?
No overload for method 'ExecuteNonQuery' takes '1' arguments
public DataTable ReturnDataTable(string str) {
return ReturnDataSet(str).Tables[0];
}
public DataRow ReturnDataRow(string str) {
return ReturnDataTable(str).Rows[0];
}
先学 SqlHelper.cs 之后再看以上高手发表的高见
myCmd.ExecuteNonQuery(CommandBehavior.CloseConnection);//这个能编译通过?
可以参考一下ms的sqlhelper是怎么写的.
public class DB
{
private static readonly string Sql = "server=127.0.0.1;uid=sa;pwd=0910;database=SFL_Property";
public SqlDataReader ReturnReader(string str)
{
using (SqlConnection conn = new SqlConnection(Sql))
{
SqlCommand myCmd = new SqlCommand(str, conn);
SqlDataReader dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
public void ExeCutSQL(string str)
{
using (SqlConnection conn = new SqlConnection(Sql))
{
SqlCommand myCmd = new SqlCommand(str, conn);
myCmd.ExecuteNonQuery(CommandBehavior.CloseConnection);
}
}
public DataSet ReturnDataSet(string str)
{
using (SqlConnection conn = new SqlConnection(Sql))
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}
public DataTable ReturnDataTable(string str)
{
using (SqlConnection conn = new SqlConnection(Sql))
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0];
} }
public DataRow ReturnDataRow(string str)
{
using (SqlConnection conn = new SqlConnection(Sql))
{
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[0].Rows[0];
}
}}