.NET数据访问架构设计和实现
【IT168 技术文档】
.NET框架为我们提供了丰富的数据访问类，利用这些类我们可以写出我们自己的数据访问架构，使得对数据库操方式作统一简单稳定。
数据库操作无非是查询、插入、更新和删除。
在.NET框架下我们通常采用的是先根据需要组织好我们要用的SQL语句然后交给数据库处理然后根据处理的结果来决定下一步的工作。问题出来了怎么才能快速安全的组织好我们需要的SQL语句？你有两种选择：
其一：采用拼凑SQL语句的方式来实现，这种方法是功能较多的不管多么复杂的逻辑都可以实现，但其繁琐且极能锻炼眼力和基本功的优点实在不敢恭维。
其二：采用技巧方法自动生成我们需要的SQL语句，但这种方法不是所有的逻辑都可以实现除非你能把这个自动生成SQL语句的类写的十分完全。这样势必会是一个庞大的工程，但这又背离了我们简单通用愿望。所以一个折中的方法就是对于复杂逻辑可以采用手工拼凑SQL语句，而对于一些常用的我们要用自动的方法来完成，而这正是解决了一个项目中的绝大的一部分工作。
组织好了我们的SQL的语句也交给了数据库去做，数据库把你的任务完成了有东西他得给我吧，没东西是好是坏也的支一声吧！可我们怎么能听的见呢？这时就需要我们用DATASET来保存数据库返回的结果集，但我们的DATASET还真就不是只有这点作用。
按照这种想法我构建出这三个类，代码如下：
public class OperatorDB
{
//public static string strCon = "workstation id=222.222.4.1;packet size=4096;user id=sa;pwd = ;data source=222.222.4.1;persist security info=False;initial catalog=EmbedRoadControls";
public static string strCon = "workstation id=222.222.4.156;packet size=4096;user id=sa;pwd = ;data source=222.222.4.156;persist security info=False;initial catalog=EmbedRoadControls";
public static string ConnectionString
{
get
{
return strCon;
}
set
{
strCon = value;
}
}
public static bool SelectData(string SqlCommandText, System.Data.DataTable Dt, bool RowsClearr)
{
bool ret = true;
if (SqlCommandText != "")
{
if (RowsClearr)
{
if (Dt.Rows.Count > 0)
{
Dt.Rows.Clear();
}
}
SqlConnection cn = new SqlConnection(strCon);
SqlDataAdapter da = new SqlDataAdapter(SqlCommandText, cn);
try
{
cn.Open();
da.Fill(Dt);
}
catch (System.Exception ex)
{
// ExceptionHand exc = new ExceptionHand(ex);
// exc.DisplayErrorMessager("OperatorDB","SelectData",SqlCommandText);
ret = false;
}
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
da.Dispose();
}
else
{
ret = false;
}
return ret;
}
public static int ExecuteSql(string SqlCommandText)
{
int ID = 0;
if (SqlCommandText != "")
{
SqlConnection cn = new SqlConnection(strCon);
SqlCommand cm = new SqlCommand(SqlCommandText, cn);
try
{
cn.Open();
ID = Convert.ToInt32(cm.ExecuteScalar());
}
catch (System.Exception ex)
{
cn.Close();
//// ExceptionHand exc = new ExceptionHand(ex);
//// exc.DisplayErrorMessager("OperatorDB","ExecuteSql",SqlCommandText);
ID = -1;
}
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
cm.Dispose();
}
return ID;
}
public static int ExecuteSql(SqlCommand Cm)
{
int ID = 0;
SqlConnection cn = new SqlConnection(strCon);
try
{
cn.Open();
Cm.Connection = cn;
ID = Convert.ToInt32(Cm.ExecuteScalar());
}
catch (System.Exception ex)
{
cn.Close();
// ExceptionHand exc = new ExceptionHand(ex);
// exc.DisplayErrorMessager("OperatorDB","ExecuteSql",Cm.CommandText);
ID = -1;
}
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
Cm.Dispose();
return ID;
}
public static void SerialNumber(int index, System.Data.DataTable dt)
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
dt.Rows[i][index] = i + 1;
}
}
public static void SetSqlCommandValues(SqlCommand Com,DataTable Dt,int Index,int StartIndex)
{
int i;
for (i=StartIndex;i<Dt.Columns.Count;i++)
{
Com.Parameters.Add("@" + Dt.Columns[i].ColumnName,Dt.Rows[Index][i]);
}
}
}
这个类是基类包含了基本的方法
现在就对方法SelectData和ExecuteSql属性strCon进行说明
strCon ：连接字符串，作用就不用多说了吧！
SelectData 的签名是public static bool SelectData(string SqlCommandText, System.Data.DataTable Dt, bool RowsClearr)
作用就是SQL语句SqlCommandText所产生的结果由Dt带回！主要就是用来查询数据的。
我用Northwind数据库举例
比如我们要查询表dbo.Customers中的数据在DATAGRID中显示只需要下面代码即可：
DataTable Dt = new DataTable();
string sqlstr = "select * from Categories";
OperatorDB.OperatorDB.SelectData(sqlstr, Dt, false);
MyDataGrid.DataSourse = Dt;
ExecuteSql作用是执行SQL语句，适用于那些不需返回结果集的语句。经过重载后可以直接接受字符串的SQL语句和SqlCommand，为了防止SQL注入我们在有些地方必须用带参数的SQL语句这时就必须用SqlCommand了。
要是在Customers中删除一条记录只需要这样即可：
OperatorDB.ExecuteSql("delete dbo.Customers where CustomerID = 'XXXXX'");
这个的其他例子我就不说了，反正都是统一的，是不是有点感觉简单许多呢？
在这里只是给出了基本的操作，也许大家会问怎么还没有自动生成我们的SQL语句，别急在介绍下面的两个类前现给大家讲点小知识。先从“视图”菜单中打开“服务器资源管理器”然后把你需要的数据库连接上，然后在我们的项目上添加个数据集把我们需要的表从服务器资源管理器中连接的数据库中直接拖放到数据集就可以了。
到现在大家也能猜的到我用什么来自动生成SQL语句了吧!
对，就是用这个定义好的DATASET,它里面已经包含了我们需要的数据库中表的信息,当我们定义DT的时候取DATASET里面表的类型就可以了.而我门只需要做的就是来控制DT然后由下面三个类中的方法负责生成相应的SQL语句：
public class InsertCommand : OperatorDB
{
public static int ExecuteInsert(System.Data.DataTable dt, int index)
{
string InsetCommandText;
SqlCommand InsertSqlCom;
int ID;
InsertSqlCom = new SqlCommand();
InsetCommandText = GetInsertCommandText(dt);
InsertSqlCom.CommandText = InsetCommandText;
SetSqlCommandValues(InsertSqlCom,dt,index,1);
ID = ExecuteSql(InsertSqlCom);
return ID;
}
public static int ExecuteInsert(System.Data.DataTable dt)
{
string InsetCommandText;
SqlCommand InsertSqlCom;
int ID =0;
InsetCommandText = GetInsertCommandText(dt);
int i;
for ( i = 0 ;i < dt.Rows.Count;i++)
{
InsertSqlCom = new SqlCommand();
InsertSqlCom.CommandText = InsetCommandText;
SetSqlCommandValues(InsertSqlCom,dt,i,1);
ID = ExecuteSql(InsertSqlCom);
}
return ID;
}
private static string GetInsertCommandText(System.Data.DataTable dt)
{
string CommandText;
string Insert;
string Values;
int ColmunCount;
int i;
ColmunCount = dt.Columns.Count;
Insert = " insert " + dt.TableName + " (" + dt.Columns[1].ColumnName;
Values = " Values ( @" + dt.Columns[1].ColumnName;
for (i = 2; i <= ColmunCount - 1; i++)
{
if (dt.Columns[i].DataType.Name != "Byte[]")
{
string ColumnName;
ColumnName = dt.Columns[i].ColumnName;
Insert = Insert + "," +ColumnName;
Values = Values + ",@" + ColumnName;
}
}
CommandText = Insert + ") " + Values + ")" + " ";
CommandText = CommandText + " SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]";
return CommandText;
}
}
更新类
public class UpdateCommand : OperatorDB
{
public static int ExecuteUpdate(System.Data.DataTable dt, int index)
{
string UpdateCommandText;
SqlCommand UpdateSqlCom;
int ID;
UpdateSqlCom = new SqlCommand();
UpdateCommandText = GetUpdateCommandText(dt);
UpdateSqlCom.CommandText = UpdateCommandText;
SetSqlCommandValues(UpdateSqlCom,dt,index,0);
ID = ExecuteSql(UpdateSqlCom);
return ID;
}
public static int ExecuteUpdate(System.Data.DataTable dt)
{
string UpdateCommandText;
SqlCommand UpdateSqlCom;
int ID = 0;
UpdateCommandText = GetUpdateCommandText(dt);
int i;
for ( i = 0 ;i < dt.Rows.Count;i++)
{
UpdateSqlCom = new SqlCommand();
UpdateSqlCom.CommandText = UpdateCommandText;
SetSqlCommandValues(UpdateSqlCom,dt,i,0);
ID = ExecuteSql(UpdateSqlCom);
}
return ID;
}
public static void UpdateTable(DataTable Dt)
{
for(int i=0;i<Dt.Rows.Count;i++)
{
if (Dt.Rows[i].RowState.ToString()=="Added")
{
InsertCommand.ExecuteInsert(Dt,i);
}
else if(Dt.Rows[i].RowState.ToString()=="Modified")
{
ExecuteUpdate(Dt,i);
}
else if(Dt.Rows[i].RowState.ToString()=="Deleted")
{
DeleteCommand.ExecuteDelete(Dt,i);
}
}
}
private static string GetUpdateCommandText(System.Data.DataTable dt)
{
string UpdateCommandText;
int ColmunCount;
int i;
ColmunCount = dt.Columns.Count;
UpdateCommandText = "Update " + dt.TableName + " set " + dt.Columns[1].ColumnName;
UpdateCommandText = UpdateCommandText + " = @" + dt.Columns[1].ColumnName;
for (i = 2; i <= ColmunCount - 1; i++)
{
if (dt.Columns[i].DataType.Name != "Byte[]")
{
string ColumnName;
ColumnName = dt.Columns[i].ColumnName;
UpdateCommandText = UpdateCommandText + "," + ColumnName + "= @" + ColumnName;
}
}
UpdateCommandText = UpdateCommandText + " where " + dt.Columns[0].ColumnName + " = @" + dt.Columns[0].ColumnName;
return UpdateCommandText;
}
}
删除类
public class DeleteCommand : OperatorDB
{
public static void ExecuteDelete(DataTable Dt,int Index)
{
string DeleteCommandText;
DeleteCommandText = GetDeleteCommandText(Dt,Index);
OperatorDB.ExecuteSql(DeleteCommandText);
}
public static void ExecuteDelete(DataTable Dt)
{
int i;
for (i = 0 ;i <Dt.Rows.Count;i++)
{
string DeleteCommandText;
DeleteCommandText = GetDeleteCommandText(Dt,i);
OperatorDB.ExecuteSql(DeleteCommandText);
}
}
public static string GetDeleteCommandText(DataTable Dt,int Index)
{
string DeleteCommandText;
DeleteCommandText = " delete " + Dt.TableName + " where " + Dt.Columns[1].ColumnName + " = "
+ Dt.Rows[Index][0] + " select -1 ";
return DeleteCommandText;
}
}
虽然.NET已经提供了类似的解决方案,但自己写出来用自己的还是很不错的一个学习过程。
