Ke leyi category

Ke leyi adopts the C #. Net Language for compiling and has powerful functions. You can use the SQL Server Connector class to efficiently and conveniently access the SQL database. Ke leyi contains three parts: execution of simple SQL statements, execution of SQL statements with parameters, and stored procedure operations.

Below is the completeness of the classCode:
Using system;
Using system. collections;
Using system. Data;
Using system. Data. sqlclient;

Namespace sqlserverdal. keleyi. com
{
/// <Summary>
/// Copyright (c) 2009-2013 keleyi.com
/// Ke leyi category
///
/// </Summary>
Public abstract class keleyisqlhelper
{
/// <Summary>
/// Modify the connection string to adapt to the specific environment
/// </Summary>
Protected static string connectionstring = "Data Source = pc-keleyi-Com; database = db_keleyi_com; user id = sa; Pwd = 123 ";
Public keleyisqlhelper ()
{
}
# Region Public Method
Public static int getmaxid (string fieldname, string tablename)
{
String strsql = "select max (" + fieldname + ") + 1 from" + tablename;
Object OBJ = getsingle (strsql );
If (OBJ = NULL)
{
Return 1;
}
Else
{
Return Int. parse (obj. tostring ());
}
}
Public static bool exists (string strsql, Params sqlparameter [] partition parms)
{
Object OBJ = getsingle (strsql, limit parms );
Int cmdresult;
If (object. Equals (OBJ, null) | (object. Equals (OBJ, system. dbnull. Value )))
{
Cmdresult = 0;
}
Else
{
Cmdresult = int. parse (obj. tostring ());
}
If (cmdresult = 0)
{
Return false;
}
Else
{
Return true;
}
}
# Endregion
# Region execute simple SQL statements
/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "sqlstring"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int executesql (string sqlstring)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Using (sqlcommand cmd = new sqlcommand (sqlstring, connection ))
{
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Connection. Close ();
Throw new exception (E. Message );
}
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstringlist"> Multiple SQL statements </param>
Public static void executesqltran (arraylist sqlstringlist)
{
Using (sqlconnection conn = new sqlconnection (connectionstring ))
{
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Sqltransaction Tx = conn. begintransaction ();
Cmd. Transaction = TX;
Try
{
For (INT n = 0; n <sqlstringlist. Count; n ++)
{
String strsql = sqlstringlist [N]. tostring ();
If (strsql. Trim (). length> 1)
{
Cmd. commandtext = strsql;
Cmd. executenonquery ();
}
}
TX. Commit ();
}
Catch (system. Data. sqlclient. sqlexception E)
{
TX. rollback ();
Throw new exception (E. Message );
}
}
}
/// <Summary>
/// Execute the SQL statement with a stored procedure parameter.
/// </Summary>
/// <Param name = "sqlstring"> SQL statement </param>
/// <Param name = "content"> parameter content. For example, a field is in a complex format. Article With special symbols, you can add them in this way </param>
/// <Returns> Number of affected records </returns>
Public static int executesql (string sqlstring, string content)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (sqlstring, connection );
System. Data. sqlclient. sqlparameter myparameter = new system. Data. sqlclient. sqlparameter ("@ content", sqldbtype. ntext );
Myparameter. value = content;
Cmd. Parameters. Add (myparameter );
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Insert an image format field into the database (another example similar to the above)
/// </Summary>
/// <Param name = "strsql"> SQL statement </param>
/// <Param name = "FS"> image byte, where the database field type is image </param>
/// <Returns> Number of affected records </returns>
Public static int executesqlinsertimg (string strsql, byte [] FS)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
System. Data. sqlclient. sqlparameter myparameter = new system. Data. sqlclient. sqlparameter ("@ FS", sqldbtype. Image );
Myparameter. value = FS;
Cmd. Parameters. Add (myparameter );
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}

/// <Summary>
/// Execute a query result statement and return the query result (object ).
// Http://www.keleyi.com/nenu/net/
/// </Summary>
/// <Param name = "sqlstring"> calculate the Query Result Statement </param>
/// <Returns> query result (object) </returns>
Public static object getsingle (string sqlstring)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Using (sqlcommand cmd = new sqlcommand (sqlstring, connection ))
{
Try
{
Connection. open ();
Object OBJ = cmd. executescalar ();
If (object. Equals (OBJ, null) | (object. Equals (OBJ, system. dbnull. Value )))
{
Return NULL;
}
Else
{
Return OBJ;
}
}
Catch (system. Data. sqlclient. sqlexception E)
{
Connection. Close ();
Throw new exception (E. Message );
}
}
}
}
/// <Summary>
/// Execute the query statement and return sqldatareader
/// </Summary>
/// <Param name = "strsql"> query statement </param>
/// <Returns> sqldatareader </returns>
Public static sqldatareader executereader (string strsql)
{
Sqlconnection connection = new sqlconnection (connectionstring );
Sqlcommand cmd = new sqlcommand (strsql, connection );
Try
{
Connection. open ();
Sqldatareader myreader = cmd. executereader ();
Return myreader;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}

}< BR >///

// run the query statement, return dataset
/// http://www.keleyi.com/nenu/net/
///
/// query statement
// dataset
Public static dataset query (string sqlstring)
{< br> using (sqlconnection connection = new sqlconnection (connectionstring)
{< br> dataset DS = new dataset ();
try
{< br> connection. open ();
sqldataadapter command = new sqldataadapter (sqlstring, connection);
command. fill (DS, "ds");
}< br> catch (system. data. sqlclient. sqlexception ex)
{< br> throw new exception (ex. message);
}< br> return Ds;
}< BR >}

# endregion
# region executes the SQL statement http://www.keleyi.com/menu/csharp/ with parameters
///

// executes the SQL statement, returns the number of affected records
/// http://www.keleyi.com/nenu/net/
///
/// SQL statement
// Number of affected records
Public static int executesql (string sqlstring, params sqlparameter [] extends parms)
{< br> using (sqlconnection connection = new sqlconnection (connectionstring ))
{< br> using (sqlcommand cmd = new sqlcommand ()
{< br> try
{< br> preparecommand (CMD, connection, null, sqlstring, writable parms);
int rows = cmd. executenonquery ();
cmd. parameters. clear ();
return rows;
}< br> catch (system. data. sqlclient. sqlexception e)
{< br> throw new exception (E. message);
}< BR >}

///

// execute multiple SQL statements to implement database transactions.
///
/// hash table of SQL statements (Key is an SQL statement, value is the sqlparameter [] of the statement.
Public static void executesqltran (hashtable sqlstringlist)
{< br> using (sqlconnection conn = new sqlconnection (connectionstring)
{< br> Conn. open ();
using (sqltransaction trans = Conn. begintransaction ()
{< br> sqlcommand cmd = new sqlcommand ();
try
{
// loop
foreach (dictionaryentry myde in sqlstringlist)
{< br> string character text = myde. key. tostring ();
sqlparameter [] parameter parms = (sqlparameter []) myde. value;
preparecommand (CMD, Conn, trans, plain text, plain parms);
int val = cmd. executenonquery ();
cmd. parameters. clear ();
trans. commit ();
}< BR >}< br> catch
{< br> trans. rollback ();
throw;
}< BR >}

///

// executes a query result statement and returns the query result (object ).
///
/// calculate the Query Result Statement
// query result (object)
Public static object getsingle (string sqlstring, Params sqlparameter [] partition parms)
{< br> using (sqlconnection connection = new sqlconnection (connectionstring ))
{< br> using (sqlcommand cmd = new sqlcommand ()
{< br> try
{< br> preparecommand (CMD, connection, null, sqlstring, callback parms);
Object OBJ = cmd. executescalar ();
cmd. parameters. clear ();
If (object. equals (OBJ, null) | (object. equals (OBJ, system. dbnull. value)
{< br> return NULL;
}< br> else
{< br> return OBJ;
}< BR >}< br> catch (system. data. sqlclient. sqlexception e)
{< br> throw new exception (E. message);
}< BR >}

/// <Summary>
/// Execute the query statement and return sqldatareader
// Http://www.keleyi.com/menu/csharp/
/// </Summary>
/// <Param name = "strsql"> query statement </param>
/// <Returns> sqldatareader </returns>
Public static sqldatareader executereader (string sqlstring, Params sqlparameter [] extends parms)
{
Sqlconnection connection = new sqlconnection (connectionstring );
Sqlcommand cmd = new sqlcommand ();
Try
{
Preparecommand (CMD, connection, null, sqlstring, callback parms );
Sqldatareader myreader = cmd. executereader ();
Cmd. Parameters. Clear ();
Return myreader;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}

}

/// <Summary>
/// Execute the query statement and return Dataset
/// </Summary>
/// <Param name = "sqlstring"> query statement </param>
/// <Returns> dataset </returns>
Public static dataset query (string sqlstring, Params sqlparameter [] partition parms)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand ();
Preparecommand (CMD, connection, null, sqlstring, callback parms );
Using (sqldataadapter da = new sqldataadapter (CMD ))
{
Dataset DS = new dataset ();
Try
{
Da. Fill (DS, "ds ");
Cmd. Parameters. Clear ();
}
Catch (system. Data. sqlclient. sqlexception ex)
{
Throw new exception (ex. Message );
}
Return Ds;
}
}
}

Private Static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction trans, string plain text, sqlparameter [] partial parms)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Cmd. Connection = conn;
Cmd. commandtext = plain text;
If (trans! = NULL)
Cmd. Transaction = trans;
Cmd. commandtype = commandtype. Text; // specify type;
If (partition parms! = NULL)
{
Foreach (sqlparameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );
}
}
# Endregion
# Region ke leyi Stored Procedure operations
/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> stored procedure parameters </param>
/// <Returns> sqldatareader </returns>
Public static sqldatareader runprocedure (string storedprocname, idataparameter [] parameters)
{
Sqlconnection connection = new sqlconnection (connectionstring );
Sqldatareader returnreader;
Connection. open ();
Sqlcommand command = buildquerycommand (connection, storedprocname, parameters );
Command. commandtype = commandtype. storedprocedure;
Returnreader = command. executereader ();
Return returnreader;
}
// Http://www.keleyi.com/menu/csharp/

///

// execute the Stored Procedure
///
/// stored procedure name
// stored procedure parameters
// name of the table in the dataset result
// dataset
Public static dataset runprocedure (string storedprocname, idataparameter [] parameters, string tablename)
{< br> using (sqlconnection connection = new sqlconnection (connectionstring ))
{< br> dataset = new dataset ();
connection. open ();
sqldataadapter sqlda = new sqldataadapter ();
sqlda. selectcommand = buildquerycommand (connection, storedprocname, parameters);
sqlda. fill (dataset, tablename);
connection. close ();
return dataset;
}< BR >}

///

// construct a sqlcommand object (used to return a result set instead of an integer)
///
/// database connection
/// stored procedure name
// stored procedure parameters
// sqlcommand
Private Static sqlcommand buildquerycommand (sqlconnection connection, string storedprocname, idataparameter [] parameters)
{< br> sqlcommand command = new sqlcommand (storedprocname, connection);
command. commandtype = commandtype. storedprocedure;
foreach (sqlparameter parameter in parameters)
{< br> command. parameters. add (parameter);
}< br> return command;
}

//

// executes the stored procedure, returned Number of affected rows
///
/// stored procedure name
// / stored procedure parameters
// Number of affected rows
///
Public static int runprocedure (string storedprocname, idataparameter [] parameters, out int rowsaffected)
{< br> using (sqlconnection connection = new sqlconnection (connectionstring)
{< br> int result;
connection. open ();
sqlcommand command = buildintcommand (connection, storedprocname, parameters);
rowsaffected = command. executenonquery ();
result = (INT) command. parameters ["returnvalue"]. value;
// connection. close ();
return result;
}< BR >}

/// <Summary>
/// Create a sqlcommand object instance (used to return an integer)
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> stored procedure parameters </param>
/// <Returns> sqlcommand object instance </returns>
Private Static sqlcommand buildintcommand (sqlconnection connection, string storedprocname, idataparameter [] parameters)
{
Sqlcommand command = buildquerycommand (connection, storedprocname, parameters );
Command. Parameters. Add (New sqlparameter ("returnvalue ",
Sqldbtype. Int, 4, parameterdirection. returnvalue,
False, 0, 0, String. Empty, datarowversion. Default, null ));
Return command;
}
# Endregion
}
}

This article reposted from Ke leyi http://www.keleyi.com/a/bjac/5e1754ac389f5fcb.htm