我为公司写了个仿三层项目,DAL层里的每个类的每个方法里大致都是按如下形式写的代码。
//构造一些本方法需要用到的Sql对象
SqlParameter[] ...
SqlCommand ...
SqlTranscation ...//执行数据库操作
using (SqlConnection conn = new SqlConnection("..."))
{
try
{
conn.Open();
...
}
catch (...)
...
}
1)为了处理数据连接的异常,比如网络问题,数据库服务未打开等,必须在每个DAL方法里面都要加上try-catch ,这真的很麻烦,有没有更好的解决办法。2)像我这样DAL层的写法有没有问题?我是仿造PetShop写的。但是没有用SqlHelper。
//构造一些本方法需要用到的Sql对象
SqlParameter[] ...
SqlCommand ...
SqlTranscation ...//执行数据库操作
using (SqlConnection conn = new SqlConnection("..."))
{
try
{
conn.Open();
...
}
catch (...)
...
}
1)为了处理数据连接的异常,比如网络问题,数据库服务未打开等,必须在每个DAL方法里面都要加上try-catch ,这真的很麻烦,有没有更好的解决办法。2)像我这样DAL层的写法有没有问题?我是仿造PetShop写的。但是没有用SqlHelper。
http://www.maticsoft.com/
报数据库连接写到一个类里边,DAL里只调用连接类就可以了,我这有个现成的 private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["OfficeString"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
} public static int ExecuteCommand(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_UpdateBooksCatagory";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
} public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
} public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
} public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
} public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public bool Insert(string sqlstr)//把sql语句传近来
{
using (SqlConnection conn = new SqlConnection("..."))
{
try
{
conn.Open();
...
}
catch (...)
...
}
}
这次你在bll层加try吧,DAL要加的话更多,而且容易昏...呵呵
1)连接数据库 ,select 某个表
2)对该表进行一些操作,其中用到SqlTranscation假如执行 1)时,数据库连接类是ok的;
接着断开数据库服务;
再执行 2)时,此时数据库连接类并不会跳转到异常处理,很奇怪,
接着SqlTranscation=conn.BeginTranscation就报错了