65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 ADO.NET 技能操作数据库

焦糖
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.83/5 (10投票s)

2010 年 2 月 9 日

CPOL

2分钟阅读

viewsIcon

35260

downloadIcon

605

在 C# 中简单使用 ADO.NET

引言

此类包含查询数据库的四个主要运算符。 使用 ADO.NET 既简单又快捷，可以节省大量时间来设计相同的代码。 它只需要大约四行代码就可以运行 SELECT、UPDATE、INSERT、DELETE 和排序功能。 但它在开发中存在一些限制，包括它建议在操作 SELECT 时只返回一行，因为如果它返回一个数据集，则必须设计一个循环来处理数据集，这不合适。

背景

然而，在使用 ADO.NET 操作数据库时，经常会出现熟悉的代码。 我想设计一个类来减少代码量。

代码扮演什么角色？

代码充当使用 ADO.NET 与 SQL Server 通信的桥梁，并且此代码适用于 Microsoft SQL Server，因为它使用 System.Data.SqlClient;

using System;
using System.Data;
using System.Data.SqlClient;

framework.JPG

适合初学者的简单数据库操作

如果范例数据库是“Yourdatabase”，表是“Table1inYourDatabase”。

Table1inYourDatabase:

Table1inYourdatabase.JPG

Table1inYourDatabase 的数据

Table1inYourdatabasedata.JPG

我们可以使用 ADO.NET 读取 Table1inYourdatabase 的数据

初始化

设置 Properties.Settings.Default.YourDatabase

properties.JPG 
//SQLDBnamespace is DLL file of SQLDB class.
using System.Data;
using SQLDBnamespace;
using System.Data.SqlClient;
//Initiate the object in global section, and already set the configuration 
//'YourDatabase' in Properties.
static SqlConnection cn = new SqlConnection(Properties.Settings.Default.YourDatabase);
SQLDB MySQLDBTable = new SQLDB(cn);

ADO.NET

cn.Open();
SqlCommand cm = new SqlCommand("SELECT * FROM Table1inYourDatabase WHERE uid1 = 1",cn);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
     {
        //get data;
     }
dr.Close();
dr.Dispose();
cm.Dispose();
cn.Close()

SQLDB

object[] SelectInTableArray = new object[7] { 1, null, null, null, null, null, null };
object[] SelectOutTableArray = new object[7] { 0, 50, 0, 1, 0, 0, 50 };
SelectOutTableArray = MySQLDBTable.SelectTable("", 0, 
	SelectInTableArray, SelectOutTableArray, "Table1inYourDatabase"); 

//SelectOutTableArray's value is { 1,1,11234,a,2009-12-01 11:04:39.000,asdf,qwer }

通过以上两种方法可以看出，ADO.NET 出现更多重复代码，而 SQLDB 使用起来更简单。 但 SQLDB 适用于返回一行的情况。

如果使用 ADO.NET 中的参数比 SQLDB 更复杂，这意味着必须编写更多参数和更多代码，但 SQLDB 可以减少代码量。

ADO.NET

cn.Open();
SqlCommand cm = new SqlCommand("SELECT * FROM Table1inYourDatabase _
			WHERE uid1 = @uid1 and uid2=@uid2", cn);
cm.Parameters.Add(new SqlParameter("@uid1", SqlDbType.Int)).Value = 1;
cm.Parameters.Add(new SqlParameter("@uid2", SqlDbType.Int)).Value = 1;
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
     {
      //get data;
     }
dr.Close();
dr.Dispose();
cm.Dispose();
cn.Close();

SQLDB

SelectInTableArray = new object[7] { 1, 1, null, null, null, null, null };
SelectOutTableArray = new object[7] { 0, 50, 0, 1, 0, 0, 50 };
SelectOutTableArray = MySQLDBTable.SelectTable
	("", 0, SelectInTableArray, SelectOutTableArray, "Table1inYourDatabase");

代码概念

我删除表的行以解释这个概念，在 ADO.NET 中

cn.Open();
SqlCommand cm = new SqlCommand("DELETE Table1inYourDatabase WHERE uid1 = @uid1", cn);
cm.Parameters.Add(new SqlParameter("@uid1", SqlDbType.Int)).Value = 3;
cm.ExecuteNonQuery();
cm.Dispose();
cn.Close();

我想使用数组来适配数据库列，并通过使用 SQLDB 类生成 ADO.NET。 首先，我使用 DeleteTableArray.Length 获取数据库列的长度。

int TableColumnLength = DeleteTableArray.Length;
object[] temp = new object[2];

并使用 GetTableColumnNameAndType 方法获取列名和类型。

string[] TableColumnName = new string[TableColumnLength];
string[] TableColumnType = new string[TableColumnLength];
temp = GetTableColumnNameAndType(DBTableName);
TableColumnName = (string[])temp[0];
TableColumnType = (string[])temp[1];

GetTableColumnNameAndType 使用 sqldatareader 的 GetName() 和 GetDataTypeName() 来获取值。 使用循环生成 SQL 查询

for (int i = 0; i < TableColumnLength; i++)
     {
       //DeleteTableArray is WHERE condition
       if (DeleteTableArray[i] != null)
         {
           // to add key word "WHERE"
           if (FirstWhere == false)
              {
               cmParameterstring += " WHERE ";
               FirstWhere = true;
              }
           //cmParameter and type only store necessary Parameters, 
           //and TableColumnName and type fit to database length.
           cmParameter[ParameterCounter] = TableColumnName[i].ToString();
           cmParameterType[ParameterCounter] = TableColumnType[i].ToString();
           cmParameterstring += TableColumnName[i].ToString() + 
		"=@" + TableColumnName[i].ToString() + " AND ";
         }
       }

我们得到 cmParameterstring，它是一个 SQL 查询，现在完成了 ADO.NET struct。

//remove last " AND "
cmParameterstring = cmParameterstring.Remove(cmParameterstring.Length - 5, 5);
cn.Open();
//ADO.NET
SqlCommand cm = new SqlCommand("DELETE FROM " + DBTableName + 
				cmParameterstring + " ", cn);
//generate SqlParameter
for (int i = 0; i < ParameterCounter; i++)
    {
     cm.Parameters.Add(new SqlParameter('@' + cmParameter[i], 
				Convert(cmParameterType[i])));
    }
//put parameter value into sqlcommand and it stores in DeleteTableArray
for (int i = 0; i < TableColumnLength; i++)
     {
      if (DeleteTableArray[i] != null)
         {
          cm.Parameters['@' + cmParameter[ParameterCounter]].Value = 
						DeleteTableArray[i];
         }
      else if (DeleteNullFlag == 1)
         {
          cm.Parameters['@' + cmParameter[ParameterCounter]].Value = DBNull.Value;
         }
      }
//SQLDB includes the open-close mechanism, that can low transaction error. 
cm.ExecuteNonQuery();
cm.Dispose();
cn.Close();

上述代码阐释了主要的逻辑，SELECT、UPDATE、INSERT 函数也类似。

对于初学者用户来说，操作可以很简单，对于程序员来说，可以节省大量时间来编写类似且繁琐的代码。 SQLDB 不能完全替代 ADO.NET，但在大多数情况下，它可以减少代码量。

Using the Code

//Delete in btnDelete_Click
protected void btnDelete_Click(object sender, EventArgs e)
{
    //ColumnSort means which column want to sort after deleting. 
    //0 means that don't sorting, 1 means that need sorting.
    //Array number is determined by 'Table1inYourDatabase' column number. 
int[] ColumnSort = new int[7] { 0, 0, 0, 0, 0, 0, 0 };
    //DeleteTableArray number is determined by 'Table1inYourdatabase' column number.  
    //object value means WHERE condition, it likes filter to find 
    //that you want to delete row.
    //That means first column value is 3 and 
    //it has to obliterate in Table1inYourdatabase.
    //and object value type is determined by your table struct. 
object[] DeleteTableArray = new object[7] {5, null, null, null, null, null, null };
    //function DeleteTable return the success flag, 
    //and first call value is SQL language but it isn't to develop,
    //second value is to scrutinize NULL value, 
    //means is column value is NULL and WHERE condition will include.
    //If second value is 0 that means omit NULL value, 
    //but 1 means NULL value is including. 
    bool DeleteFlag = MySQLDBTable.DeleteTable
		    ("", 0, ColumnSort, DeleteTableArray, "Table1inYourDatabase"); 
}
//Update btnUpdate_Click
protected void btnUpdate_Click (object sender, EventArgs e)
{ 
    // UpdateInTableArray means WHERE condition, 
    // UpdateSetTableArray means in filter condition to SET value. 
    //If you want to SET value to null, you have to use "NULL" 
    //in order to discriminate value null. 
    object[] UpdateInTableArray = new object[7] { 3, null, null, null, null, null, null };
    object[] UpdateSetTableArray = new object[7] 
			{ null,"1234",1234, "NULL", null, null, null };
    //UpdateTable function is like DeleteTable. 
    bool UpdateFlag = MySQLDBTable.UpdateTable
	("", 0, UpdateInTableArray, UpdateSetTableArray, "Table1inYourDatabase"); 
}
//Insert btnInsert_Click 
protected void btnInsert_Click (object sender, EventArgs e)
{ 
    //PKFlag means which column is primary key, you have to give column value 1, 
    //if it is a primary key, other column set 0 in this array. 
    int[] PKFlag = new int[7];
    //insert a new row 3, "5678" 
    object[] InsertTableArray = new object[7] { 6, "5678", null, null, null, null, null };
    bool InsertFlag = MySQLDBTable.InsertTable
	("", PKFlag, InsertTableArray, "Table1inYourDatabase"); 
}
//Select btnSelect_Click  
protected void btnSelect_Click(object sender, EventArgs e)
{
    //METHOD 1 return a row 
    //SelectInTableArray is WHERE condition 
    object[] SelectInTableArray = new object[7] { 1, null, null, null, null, null, null };
    //SelectOutTableArray is that you want return value, 
    //if column type is int , use 0, if it is a string or have char size,
    //you must to set the size value , for example, 
    //15 means sixth column type is nvarchar and size is 15. 
    object[] SelectOutTableArray = new object[7] { null, null, null, null, 0, 0, 50 };
    SelectOutTableArray = MySQLDBTable.SelectTable("", 0, 
	SelectInTableArray, SelectOutTableArray, "Table1inYourDatabase"); 
      
    //METHOD 2 return a dataset
    //SelectInTableArray and SelectOutTableArray are not functional in this case. 
    SelectInTableArray = new object[1]; 
    SelectOutTableArray = new object[1];
    //SelectOutTableArray2 is to receive the dataset. 
    object[,] SelectOutTableArray2 = new object[0,0]; 
    SelectOutTableArray = MySQLDBTable.SelectTable
	("SELECT DISTINCT uid, uid2, uid3 FROM Table1inYourDatabase WHERE uid>3", 
	0, SelectInTableArray, SelectOutTableArray, "");
    SelectOutTableArray2 = (object[,])SelectOutTableArray[0];
    //SelectOutTableArray2's length is determined by SELECT column number. 
    for (int i = 0; i < SelectOutTableArray2.Length / 3; i++)
        {
            dropdownlist1.Items.Add(SelectOutTableArray2[i, 0].ToString() + 
		"\t" + SelectOutTableArray2[i, 1].ToString() + "\t" + 
		SelectOutTableArray2[i, 2].ToString());
        }
 } 
//Sort function is like above function to use.
//bool InsertFlag = MySQLDBTable.SortTable( ColumnSort, "Table1inYourDatabase" );

关注点

为了提高编码速度。

历史

  • 2010.02.03 初始版本
© . All rights reserved.