Using ADO.NET Skill to Operate the Database

• Download source - 30.96 KB

Introduction

This class includes main four operators in querying database. It is simple and faster to use ADO.NET and relinquish a lot of time to design the same code. It just utilizes almost four lines of code and can run SELECT, UPDATE, INSERT, DELETE, and sorting function. But it has some limitations in development including that it suggests to get one row in return on operating SELECT because if it returns a dataset, you have to design a loop to address the dataset, it is not suitable.

Background

To use ADO.NET in operating database, however, the familiar code usually appears. I want to design a class to lower the code size.

Which Role Does the Code Play?

The code acts as a bridge to use ADO.NET to communicate with the SQL Server, and this code is suitable for Microsoft SQL Server, because it uses System.Data.SqlClient;

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

Simple to Operate Database for Beginner

If paradigm database is "Yourdatabase" and table is "Table1inYourDatabase".

Table1inYourDatabase:

Table1inYourDatabase's data:

We can use ADO.NET to read Table1inYourdatabase's data:

Initialize:

Set Properties.Settings.Default.YourDatabase:

//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 }

The above two methods can know that ADO.NET have more repeat code to appear, and SQLDB is simpler to use. but SQLDB is suitable for a row in return.

If using parameters in ADO.NET is more complicated than SQLDB, that means more parameters and more code has to be written, but SQLDB can decrease the size.

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"); 

Code Conception

I do Delete table's row to interpret the conception, in 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();

I want to use array to fit database columns and generate ADO.NET by using SQLDB class. First I use DeleteTableArray.Length to get database column length.

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

And use GetTableColumnNameAndType method to get columnname and type.

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

GetTableColumnNameAndType uses sqldatareader's GetName() and GetDataTypeName() to attain value. Using loops to generate SQL query:

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 ";
         }
       }

We get cmParameterstring, it is a SQL query and now completes the 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();

The above code paraphrases primate logicality and SELECT, UPDATE, INSERT functions are similar.

For a beginner user, that can be simple to operate and for a programmer, that can spare a lot of time to write similar and cumbersome code. SQLDB can't completely substitute ADO.NET, but at most part it can retrench code size.

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" ); 

Points of Interest

TO boost coding velocity.

History

• 2010.02.03 Initial version