数据访问层

来源：互联网发布：java工程师是什么意思编辑：程序博客网时间：2024/06/05 17:05

private void Form1_Load(object sender, EventArgs e)
{
//企业级框架与此逻辑一样。系统框架不用任意灵活这样性能会很差。
//用框架稍作修改就可以支持其它的数据库
string CommandText = "insert into (name,sex) values (@name,@sex)";
DbParameter[] Fileds = new DbParameter[2];
Fileds[0].ParameterName = "name";
Fileds[0].Value = "1";
Fileds[1].ParameterName = "sex";
Fileds[1].Value = "0";
DataDb.ExecuteNonQuery(new SessionFactory(), CommandText, Fileds);
CommandText = null;
Fileds = null;
//方法2直接通过控件或是Form里面的值向参数中赋值。
CommandText = "insert into (name,sex) values (@name,@sex)";
Fileds = FromGetCommand(this, CommandText);
DataDb.ExecuteNonQuery(new SessionFactory(), CommandText, Fileds);
CommandText = null;
Fileds = null;
}
public DbParameter[] FromGetCommand(Form f, string CommandText)
{
//建立一张表做验证与格式的检测
return null;
}

public class SessionFactory
{
public string UserName { get; set; }
}
public class DataDb
{
private static readonly string connstr = string.Empty;
//简单语句
public static bool ExecuteNonQuery(SessionFactory Session, string CommandText, DbParameter[] Fileds)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
return command.ExecuteNonQuery() > 0;
}
}
catch
{
}
finally
{
conn.Close();
}
}
return false;
}
public static SqlDataReader ExecuteReader(SessionFactory Session, string CommandText, DbParameter[] Fileds)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
public static DataSet ExecuteDataset(SessionFactory Session, string CommandText, DbParameter[] Fileds)
{
DataSet ds = null;
using (SqlConnection conn = new SqlConnection(connstr))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
DataAdapter.Fill(ds);
return ds;
}
}
catch
{
}
finally
{
conn.Close();
}
}
return ds;
}
public static DataTable ExecuteDataTable(SessionFactory Session, string CommandText, DbParameter[] Fileds)
{
DataTable dt = null;
using (SqlConnection conn = new SqlConnection(connstr))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
DataAdapter.Fill(dt);
return dt;
}
}
catch
{
}
finally
{
conn.Close();
}
}
return dt;
}
public static object ExecuteScalar(SessionFactory Session, string CommandText, DbParameter[] Fileds)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
try
{
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
return command.ExecuteScalar();
}
}
catch
{
}
finally
{
conn.Close();
}
}
return null;
}
//事务语句
public static bool ExecuteNonQuery(SessionFactory Session, string CommandText, DbParameter[] Fileds, SqlTransaction Transaction)
{
using (SqlCommand command = new SqlCommand())
{
command.Transaction = Transaction;
command.Connection = Transaction.Connection;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
return command.ExecuteNonQuery() > 0;
}
}
//在事务中建议先把资源存储进行事务处理
//public static SqlDataReader ExecuteReader(SessionFactory Session, string CommandText, DbParameter[] Fileds, SqlTransaction Transaction)
//{
// using (SqlCommand command = new SqlCommand())
// {
// command.Transaction = Transaction;
// command.Connection = Transaction.Connection;
// command.CommandType = CommandType.Text;
// command.CommandText = CommandText;
// command.Parameters.Add(Fileds);
// return command.ExecuteReader(CommandBehavior.CloseConnection);
// }
//}
//public static DataSet ExecuteDataset(SessionFactory Session, string CommandText, DbParameter[] Fileds, SqlTransaction Transaction)
//{
// DataSet ds = null;
// using (SqlCommand command = new SqlCommand())
// {
// command.Transaction = Transaction;
// command.Connection = Transaction.Connection;
// command.CommandType = CommandType.Text;
// command.CommandText = CommandText;
// command.Parameters.Add(Fileds);
// SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
// DataAdapter.Fill(ds);
// return ds;
// }
//}
//public static DataTable ExecuteDataTable(SessionFactory Session, string CommandText, DbParameter[] Fileds, SqlTransaction Transaction)
//{
// DataTable dt = null;

// using (SqlCommand command = new SqlCommand())
// {
// command.Transaction = Transaction;
// command.Connection = Transaction.Connection;
// command.CommandText = CommandText;
// command.Parameters.Add(Fileds);
// SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
// DataAdapter.Fill(dt);
// return dt;
// }
//}
public static object ExecuteScalar(SessionFactory Session, string CommandText, DbParameter[] Fileds, SqlTransaction Transaction)
{
using (SqlCommand command = new SqlCommand())
{
command.Transaction = Transaction;
command.Connection = Transaction.Connection;
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
command.Parameters.Add(Fileds);
return command.ExecuteScalar();
}
}
}

1 0