C # Database Operations (General)

Using system;

Using system. Collections. Generic;

Using system. text;

Using system. Data. sqlclient;

Using system. configuration;

Using system. Data;

 

Namespace MSSQL database operations

{

Public class sqlserver

{

 

Sqlconnection _ con = new sqlconnection ();

Sqlcommand _ cmd = new sqlcommand ();

Sqldataadapter _ SDA = new sqldataadapter ();

 

Public sqlserver ()

: This (configurationmanager. connectionstrings ["mssqlconstr"]. connectionstring)

{

}

 

Public sqlserver (string connectionstring)

{

This. _ con. connectionstring = connectionstring;

This. _ cmd. Connection = This. _ con;

}

 

Public void reset ()

{

This. _ cmd. Connection = This. _ con;

This. _ cmd. commandtype = commandtype. text;

This. SQL = string. empty;

This. Parameters. Clear ();

}

 

Public void open ()

{

If (this. _ con. State = connectionstate. Closed) This. _ con. open ();

}

 

Public void close ()

{

If (this. _ con. State = connectionstate. Open) This. _ con. Close ();

}

 

Public commandtype

{

Get

{

Return this. _ cmd. commandtype;

}

Set

{

This. _ cmd. commandtype = value;

}

}

 

Public String SQL

{

Get

{

Return this. _ cmd. commandtext;

}

Set

{

This. _ cmd. commandtext = value;

}

}

 

Public sqlparametercollection Parameters

{

Get

{

Return this. _ cmd. parameters;

}

}

 

Public String getdeletesql (string tablename, string condition)

{

If (! String. isnullorempty (condition ))

{

Condition = "where" + condition;

}

Return string. Format ("delete from {0} {1}", tablename, condition );

}

// Select top number fields from tablename where Condition

Public String getselectsql (string table, string condition, int top, string fields, string orderby)

{

# Region is not a good version

// String SQL = "select ";

// If (top> 1)

//{

// SQL + = "TOP" + TOP. tostring () + "";

//}

// If (! String. isnullorempty (fields ))

//{

// SQL + = fields;

//}

// Else

//{

// SQL + = "* from ";

//}

// SQL + = tablename;

// If (! String. isnullorempty (condition ))

//{

// Condition = "where" + condition;

//}

 

// SQL + = condition;

 

// If (! String. isnullorempty (orderby ))

//{

// Orderby = "order by" + orderby;

//}

 

// SQL + = orderby;

# Endregion

 

String T = top> 0? "TOP" + TOP. tostring ():"";

 

If (string. isnullorempty (fields ))

{

Fields = "*";

}

If (! String. isnullorempty (condition ))

{

Condition = "where" + condition;

}

If (! String. isnullorempty (orderby ))

{

Orderby = "order by" + orderby;

}

 

String SQL = string. Format ("select {0} {1} from {2} {3} {4}", T, fields, table, condition, orderby );

 

Return SQL;

 

}

 

Public String getselectsql (string table, string condition, stringfields, string orderby)

{

Return this. getselectsql (table, condition, 0, fields, orderby );

}

 

Public String getgagingselectsql (string table, string condition, stringfields, string orderby)

{

String SQL = @ "select * from (

Select row_number () over (order by {0}) as rownum, {1} from {2} {3}

) AST

Where rownumbetween (@ CurrentPage-1) * @ pagesize + 1 and @ currentpage * @ pagesize order by {0 }";

 

If (! String. isnullorempty (condition ))

{

Condition = "where" + condition;

}

If (string. isnullorempty (fields ))

{

Fields = "*";

}

 

Return string. Format (SQL, orderby, fields, table, condition );

 

}

 

Public String getinsertsql (string table)

{

// Insert into student (SNO, sname, ssex, sage, sdept) values (@ SnO, @ sname, @ ssex, @ sage, @ sdept)

String SQL = "insert into {0} ({1}) values ({2 })";

String Field = string. empty;

String parameter = string. empty;

Foreach (sqlparameter SPR in this. Parameters)

{

Field + = SPR. parametername. Remove (0, 1) + ",";

Parameter + = SPR. parametername + ",";

}

Field = field. Remove (field. Length-1, 1 );

Parameter = parameter. Remove (parameter. Length-1, 1 );

Return string. Format (SQL, table, field, parameter );

}

 

Public String getupdatesql (string table, string condition)

{

// Update student set SnO = @ SnO, sname = @ sname where SnO = @ SnO and .....

String SQL = "Update {0} set {1} {2 }";

String STR = string. empty;

Foreach (sqlparameter SPR in this. Parameters)

{

STR + = SPR. parametername. Remove (0, 1) + "=" + SPR. parametername + ",";

}

STR = Str. Remove (Str. Length-1, 1 );

If (! String. isnullorempty (condition ))

{

Condition = "where" + condition;

}

Return string. Format (SQL, table, STR, condition );

}

 

/// <Summary>

/// Add sqlparameter. Five attribute values are required.

/// </Summary>

/// <Param name = "parametername"> parameter name </param>

/// <Param name = "sqldbtype"> parameter data type </param>

/// <Param name = "size"> size </param>

/// <Param name = "sourcecolumn"> source column name </param>

/// <Param name = "value"> value </param>

/// <Returns> added parameter object </returns>

Public sqlparameter addsqlparameter (string parametername, sqldbtypesqldbtype, int size, string sourcecolumn, object value)

{

Sqlparameter SPR = new sqlparameter ();

 

Spr. parametername = parametername;

Spr. sqldbtype = sqldbtype;

 

If (size> 0)

{

Spr. size = size;

}

 

If (! String. isnullorempty (sourcecolumn ))

{

Spr. sourcecolumn = sourcecolumn;

}

 

Spr. value = (value = NULL )? Dbnull. Value: value;

Return this. _ cmd. Parameters. Add (SPR );

 

}

 

 

/// <Summary>

/// Add sqlparameter. Four attribute values are required.

/// </Summary>

/// <Param name = "parametername"> parameter name </param>

/// <Param name = "sqldbtype"> parameter data type </param>

/// <Param name = "sourcecolumn"> source column name </param>

/// <Param name = "value"> value </param>

/// <Returns> added parameter object </returns>

Public sqlparameter addsqlparameter (string parametername, sqldbtypesqldbtype, string sourcecolumn, object value)

{

Return this. addsqlparameter (parametername, sqldbtype, 0, sourcecolumn, value );

}

 

/// <Summary>

/// Add sqlparameter. Three attribute values are required.

/// </Summary>

/// <Param name = "parametername"> parameter name </param>

/// <Param name = "sqldbtype"> parameter data type </param>

/// <Param name = "value"> value </param>

/// <Returns> added parameter object </returns>

Public sqlparameter addsqlparameter (string parametername, sqldbtypesqldbtype, object value)

{

Return this. addsqlparameter (parametername, sqldbtype, null, value );

}

 

/// <Summary>

/// Add sqlparameter. Two attribute values are required.

/// </Summary>

/// <Param name = "parametername"> parameter name </param>

/// <Param name = "sqldbtype"> parameter data type </param>

/// <Param name = "value"> value </param>

/// <Returns> added parameter object </returns>

Public sqlparameter addsqlparameter (string parametername, object value)

{

Sqlparameter SPR = new sqlparameter ();

Spr. parametername = parametername;

Spr. value = (value = NULL )? Dbnull. Value: value;

Return this. _ cmd. Parameters. Add (SPR );

}

 

Public void clearsqlparameter ()

{

This. _ cmd. Parameters. Clear ();

}

 

 

# Region executes update, insert, and delete, and returns the number of affected rows

Public int excutenonquery ()

{

Try

{

This. open ();

Return _ cmd. executenonquery ();

 

}

Catch (exception ex)

{

Throw ex;

}

Finally

{

This. Close ();

}

}

 

# Endregion

 

# Region execute the select count (*) from... similar SQL statement and return the first column of the First Line

Public object excutescalar ()

{

Try

{

This. open ();

 

Return _ cmd. executescalar ();

 

}

Catch (exception ex)

{

Throw ex;

}

Finally

{

This. Close ();

}

}

# Endregion

 

# Region execute the select attribute list from... similar SQL statement and return the first column of the First Line

Public sqldatareader excutereader ()

{

Try

{

 

This. open ();

 

Return _ cmd. executereader ();

 

}

Catch (exception ex)

{

Throw ex;

}

Finally

{

// If (con. State = connectionstate. Open) con. Close (); // you can use the delegate mechanism to solve the problem that data cannot be closed.

}

}

# Endregion

 

# Region execute the select attribute list from... similar SQL statement and return a database in memory

Public dataset getdataset ()

{

Try

{

 

This. _ SDA. selectcommand = This. _ cmd;

 

Dataset DS = new dataset ();

This. _ SDA. Fill (DS );

Return Ds;

}

Catch (exception ex)

{

Throw ex;

}

Finally

{

 

}

}

# Endregion

 

# Region has a defect in writing. The returned data table cannot be merged with other databases at will because it is related to DS.

// Public datatable getdatatable (string SQL)

//{

// Dataset DS = This. getdataset (SQL );

// Return Ds. Tables [0];

//}

# Endregion

 

 

# Region execute the select attribute list from... similar SQL statement to return a data table in memory

Public datatable getdatatable ()

{

Try

{

 

This. _ SDA. selectcommand = This. _ cmd;

 

Datatable dt = new datatable ();

This. _ SDA. Fill (DT );

Return DT;

}

Catch (exception ex)

{

Throw ex;

}

Finally

{

 

}

}

# Endregion

 

 

}

}