/*************************************** ************************************ Instructions
** 1. Obtain the database connection. The returned value must be null ----------- getsqlconnection.
* 2. Return dataset --------------------- getdataset according to the select -- query statement.
* 3. Fill the DataGrid -------------- filldatagridfromsqlstring with the database content
* 4. Use the database content to fill the DataGrid -------------- filldatagridfromsqlstring (reload)
* 5. Return the number of rows queried by the SQL statement ----------------------------- getrowcount
* 6. Fill in the drop-down list -------------------------------------- fillcombobox *
7. A datareader is generated by an SQL statement. The returned value must be null. ------ getdatareader
* 8. Return a single query data: the value of the first row in the first column --------------- getfirstdata
* 9. operation on a record in the database: add, delete, and update ------------- executecommand
* 10. add, delete, modify, and delete a database ------------------------- executecommand2
* 11. Determine if STR is composed of digits ------------------------- isnumeric
* 12. Check the actual length of the string containing Chinese characters ---------------------- Len
**************************************** ***********************************/
Using system;
Using system. Data. sqlclient;
Using system. Data;
Using system. Windows. forms;
Namespace lateral side. Common
{
/// <Summary>
/// Summary of the database.
/// </Summary>
Public class database
{
Public Database ()
{
}
/// <Summary>
/// 1. Obtain the database connection. The returned value must be null.
/// </Summary>
/// <Returns> </returns>
Public static sqlconnection getsqlconnection ()
{
String strcnn = "Server = 192.168.12.136; database = lateral side; user id = sa; Password = ";
Try
{
Sqlconnection sqlcnn = new sqlconnection (strcnn );
Sqlcnn. open ();
Return sqlcnn;
}
Catch (exception ee)
{
String temp = ee. message;
Return NULL;
}
}
/// <Summary>
/// Obtain the sqlcommand object
/// </Summary>
/// <Returns> </returns>
Public static sqlcommand getsqlcommand ()
{
Sqlconnection sqlcnn = getsqlconnection ();
If (sqlcnn = NULL)
Return NULL;
Else
{
Sqlcommand sqlcmm = new sqlcommand ();
Sqlcmm. Connection = sqlcnn;
Return sqlcmm;
}
}
/// <Summary>
/// 2. Return dataset according to the select-query statement
/// </Summary>
/// <Param name = "strsql"> select SQL statement </param>
/// <Returns> whether the returned value is null must be determined. </returns>
Public static dataset getdataset (string strsql)
{
Try
{
Using (sqlconnection sqlcnn = getsqlconnection ())
{
Sqldataadapter dataadapter = new sqldataadapter (strsql, sqlcnn );
Dataset dataset = new dataset ();
Dataadapter. Fill (Dataset );
Return dataset;
}
}
Catch
{
Return NULL;
}
}
/// <Summary>
/// 3. Fill the DataGrid with the database content
/// </Summary>
/// <Param name = "DataGrid"> DataGrid to be filled </param>
/// <Param name = "strsql"> obtain the SQL string of the database content. </param>
/// <Returns> </returns>
Public static bool filldatagridfromsqlstring (DataGrid, string strsql)
{
Try
{
Dataset DS = getdataset (strsql );
DataGrid. setdatabinding (DS ,"");
Return true;
}
Catch (exception ee)
{
String T = ee. message;
Return false;
}
}
/// <Summary>
/// 4. Use the database content to fill the DataGrid
/// </Summary>
/// <Param name = "DataGrid"> DataGrid to be filled </param>
/// <Param name = "strsql"> obtain the SQL string of the database content. </param>
/// <Param name = "table"> name of the table to be filled with the DataGrid </param>
/// <Returns> </returns>
Public static bool filldatagridfromsqlstring (DataGrid, string strsql, string table)
{
Try
{
Dataset DS = getdataset (strsql );
DataGrid. setdatabinding (DS, table );
Return true;
}
Catch (exception ee)
{
String T = ee. message;
Return false;
}
}
/// <Summary>
/// 5. Return the number of rows queried by the SQL statement.
/// </Summary>
/// <Param name = "strsql"> </param>
/// <Returns> </returns>
Public static int getrowcount (string strsql)
{
Dataset DS = getdataset (strsql );
Int COUNT = Ds. Tables [0]. Rows. count;
Return count;
}
/// <Summary>
/// 6. Fill in the drop-down list
/// </Summary>
/// <Param name = "cmbox"> added ComboBox </param>
/// <Param name = "strsql"> query statement </param>
/// <Returns> Successful </returns>
Public static bool fillcombobox (ComboBox cmbox, string strsql)
{
Try
{
Using (sqlconnection sqlcnn = getsqlconnection ())
{
Sqldatareader DR = getdatareader (strsql );
While (dr. Read ())
{
Cmbox. Items. Add (dr. getvalue (0 ));
}
Return true;
}
}
Catch
{
Return false;
}
}
/// <Summary>
/// 7. A datareader is generated by an SQL statement. The returned value must be null.
/// </Summary>
/// <Param name = "strsql"> SQL statement to be used </param>
/// <Returns> </returns>
Public static sqldatareader getdatareader (string strsql)
{
Try
{
Sqlconnection sqlcnn = getsqlconnection ();
Sqlcommand sqlcmm = new sqlcommand (strsql, sqlcnn );
Return sqlcmm. executereader (commandbehavior. closeconnection );
}
Catch
{
Return NULL;
}
}
/// <Summary>
/// 8. Return a single query data: the value of the first column and the first row
/// </Summary>
/// <Param name = "strsql"> select SQL statement </param>
/// <Returns> </returns>
Public static string getfirstdata (string strsql)
{
Try
{
Using (sqlconnection sqlcnn = getsqlconnection ())
{
Sqlcommand sqlcmm = new sqlcommand (strsql, sqlcnn );
Return sqlcmm. executescalar (). tostring ();
}
}
Catch
{
Return "";
}
}
/// <Summary>
/// 9. operate on a record in the database: add, delete, and update
/// </Summary>
/// <Param name = "strsql"> SQL statement to be executed </param>
/// <Returns> whether execution is successful </returns>
Public static bool executecommand (string strsql)
{
Try
{
Using (sqlconnection sqlcnn = getsqlconnection ())
{
Sqlcommand sqlcmm = new sqlcommand (strsql, sqlcnn );
Int temp = sqlcmm. executenonquery ();
Return temp = 1;
}
}
Catch
{
Return false;
}
}
/// <Summary>
/// 10. add, delete, and modify a database
/// </Summary>
/// <Param name = "strsql"> </param>
/// <Returns> </returns>
Public static bool executecommand2 (string strsql)
{
Try
{
Using (sqlconnection sqlcnn = getsqlconnection ())
{
Sqlcommand sqlcmm = new sqlcommand (strsql, sqlcnn );
Int temp = sqlcmm. executenonquery ();
Return true;
}
}
Catch
{
Return false;
}
}
/// <Summary>
/// 11. Check whether STR is composed of digits.
/// </Summary>
/// <Param name = "str"> </param>
/// <Returns> </returns>
Public static bool isnumeric (string Str)
{
If (STR = NULL | Str. Length = 0)
Return false;
Foreach (char C in Str)
{
If (! Char. isnumber (c ))
{
Return false;
}
}
Return true;
}
/// <Summary>
/// 12. Check the actual length of the string containing Chinese Characters
/// </Summary>
/// <Param name = "str"> string </param>
Public static int Len (string Str)
{
System. Text. asciiencoding n = new system. Text. asciiencoding ();
Byte [] B = n. getbytes (STR );
Int L = 0; // L is the actual length of the string
For (INT I = 0; I <= B. Length-1; I ++)
{
If (B [I] = 63) // determines whether it is a Chinese character or full-legged symbol.
{
L ++;
}
L ++;
}
Return L;
}}
}