using ADO.NET skill to operate the database - SQLDB @ Samuel

自己寫的ADO.NET class

使用C#

還蠻實用的

操作與講解都寫在

http://www.codeproject.com/KB/aspnet/SQLDB.aspx

#終於成功被這個網站審核過了

有提供下載

Introduction

This class includes main four operator 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 line code and can run SELECT, UPDATE, INSERT, DELETE, and sorting function. But it have some limit in now development including that it suggest to get one row in return on operating SELECT, Because if it return 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 is usually appear, I want to design a class to low the code size.

Which role do the code play?

The code play a bridge to use ADO.NET to communicate with SQL sever, and this code is suitable for MS SQL Server, because it use System.Data.SqlClient;

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

SQLDB

Simply to operate database for beginner

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

Table1inYourDatabase:

SQLDB

Table1inYourDatabase's data:
SQLDB

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

Initialize:

Set Properties.Settings.Default.YourDatabase:

SQLDB

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

Above two method 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 complicate than SQLDB. That means more parameters and more code have to write, 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 is use 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 complete 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();

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

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

Using the code

//Delete 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 have 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 sucess 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 oder 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" );