Asp tutorial. net database tutorial connection class
<% @ Page Language = "C #" ContentType = "text/html" ResponseEncoding = "gb2312" %>
<Html xmlns = "http://www.bKjia. c0m/1999/xhtml">
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312"/>
<Title> asp.net tutorial database connection class </title>
</Head>
<Body>
Private SqlCommand CreateCommand (string ProcName, SqlParameter [] Prams)
{
Open ();
SqlCommand Cmd = new SqlCommand (ProcName, Connection );
Cmd. CommandType = CommandType. StoredProcedure;
If (Prams! = Null)
{
Foreach (SqlParameter Parameter in Prams)
Cmd. Parameters. Add (Parameter );
}
Return Cmd;
}
// Public method: instantiate a parameter used to call a stored procedure
// Input:
// ParamName-Parameter Name
// DbType-parameter type
// Size-parameter Size
// Direction-transfer Direction
// Value-Value
Public SqlParameter MakeParam (string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter Param;
If (Size> 0)
Param = new SqlParameter (ParamName, DbType, Size );
Else Param = new SqlParameter (ParamName, DbType );
Param. Direction = Direction;
If (Value! = Null)
Param. Value = Value;
Return Param;
}
// Public method: instantiate an input parameter used to call a stored procedure
// Input:
// ParamName-Parameter Name
// DbType-parameter type
// Size-parameter Size
// Value-Value
Public SqlParameter MakeInParam (string ParamName, SqlDbType DbType, int Size, object Value)
{
Return MakeParam (ParamName, DbType, Size, ParameterDirection. Input, Value );
}
// A public method that calls a stored procedure (without parameters)
// Input:
// Name of the ProcName Stored Procedure
// Output:
// The number of rows affected by the Update, Insert, and Delete operations. In other cases, the value is-1.
Public int RunProc (string ProcName)
{
Int Count =-1;
SqlCommand Cmd = CreateCommand (ProcName, null );
Count = Cmd. ExecuteNonQuery ();
Close ();
Return Count;
}
// A public method that calls a stored procedure (with parameters)
// Input:
// ProcName-name of the stored procedure
// Params-parameter table used to call a stored procedure
// Output:
// The number of rows affected by the Update, Insert, and Delete operations. In other cases, the value is-1.
Public int RunProc (string ProcName, SqlParameter [] Params)
{
Int Count =-1;
SqlCommand Cmd = CreateCommand (ProcName, Params );
Count = Cmd. ExecuteNonQuery ();
Close ();
Return Count;
}
// A public method that calls a stored procedure (without parameters)
// Input:
// Name of the ProcName Stored Procedure
// Output:
// Return the execution result as SqlDataReader
// Note: Call the SqlDataReader. Close () method after use.
Public SqlDataReader RunProcGetReader (string ProcName)
{
SqlCommand Cmd = CreateCommand (ProcName, null );
Return Cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
}
// A public method that calls a stored procedure (without parameters)
// Input:
// Name of the ProcName Stored Procedure
// Output:
// Return the execution result as SqlDataReader
// Note: Call the SqlDataReader. Close () method after use.
Public string RunProcGetScalar (string ProcName, SqlParameter [] Params)
{
SqlCommand Cmd = CreateCommand (ProcName, Params );
// SqlDataAdapter adapter = new SqlDataAdapter (Cmd );
// DataSet dataset = new DataSet ();
// Adapter. Fill (dataset );
// Close ();
// Return dataset;
Return Convert. ToString (Cmd. ExecuteScalar ());
}
// A public method that calls a stored procedure (with parameters)
// Input:
// ProcName-name of the stored procedure
// Params-parameters required for the stored procedure
// Output:
// Return the execution result as SqlDataReader
// Note: Call the SqlDataReader. Close () method after use.
Public SqlDataReader RunProcGetReader (string ProcName, SqlParameter [] Params)
{
SqlCommand Cmd = CreateCommand (ProcName, Params );
Return Cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
}
// A public method that calls a stored procedure (with parameters)
// Input:
// ProcName-name of the stored procedure
// Params-parameters required for the stored procedure
// Output:
// Return the execution result as SqlDataReader
// Note: Call the SqlDataReader. Close () method after use.
Public int RunProcGetCount (string ProcName, SqlParameter [] Params)
{
SqlCommand Cmd = CreateCommand (ProcName, Params );
String SCount;
SCount = Cmd. ExecuteScalar (). ToString (). Trim ();
If (SCount = "")
SCount = "0 ";
Close ();
Return Convert. ToInt32 (SCount );
}
// A public method that calls a stored procedure (without parameters)
// Input:
// Name of the ProcName Stored Procedure
// Output:
// Return the execution result as DataSet
Public DataSet GetDataSet (string ProcName, SqlParameter [] Params)
{
// Open ();
SqlCommand Cmd = CreateCommand (ProcName, Params );
SqlDataAdapter adapter = new SqlDataAdapter (Cmd );
DataSet dataset = new DataSet ();
Adapter. Fill (dataset );
Close ();
Return dataset;
}
// Public method. A result dataset is returned Based on the SQL statement.
Public DataSet GetDataSetSql (string XSqlString)
{
Open ();
SqlDataAdapter Adapter = new SqlDataAdapter (XSqlString, Connection );
DataSet Ds = new DataSet ();
Adapter. Fill (Ds );
Close ();
Return Ds;
}
// Public method, insert records according to SQL statements
Public int Insert (string XSqlString)
{
Int Count =-1;
Open ();
SqlCommand cmd = new SqlCommand (XSqlString, Connection );
Count = cmd. ExecuteNonQuery ();
Close ();
Return Count;
}
// Public method. Insert a record and return the generated ID number based on the SQL statement.
Public int GetIDInsert (string XSqlString)
{
Int Count =-1;
Open ();
SqlCommand cmd = new SqlCommand (XSqlString, Connection );
Count = int. Parse (cmd. ExecuteScalar (). ToString (). Trim ());
Close ();
Return Count;
}
}
}
</Body>
</Html>
Database Connection Class 2
Public bool GetRecord (string XSqlString)
{
Open ();
SqlDataAdapter adapter = new SqlDataAdapter (XSqlString, Connection );
DataSet dataset = new DataSet ();
Adapter. Fill (dataset );
Close ();
If (dataset. Tables [0]. Rows. Count> 0)
{
Return true;
}
Else
{
Return false;
}
}
// Public method, return the data value obtained by the SQL statement
// SqlString format: select count (*) from XXX where...
// Select max (XXX) from YYY where...
Public int GetRecordCount (string XSqlString)
{
String SCount;
Open ();
SqlCommand Cmd = new SqlCommand (XSqlString, Connection );
SCount = Cmd. ExecuteScalar (). ToString (). Trim ();
If (SCount = "")
SCount = "0 ";
Close ();
Return Convert. ToInt32 (SCount );
}
// Public method, which updates some records in the XTableName table based on XWhere
// XTableName -- table name
// XHT -- hash table. The key is the field name and the value is the field value.
Public DataSet AdvancedSearch (string XTableName, Hashtable XHT)
{
Int Count = 0;
String Fields = "";
Foreach (DictionaryEntry Item in XHT)
{
If (Count! = 0)
{
Fields + = "and ";
}
Fields + = Item. Key. ToString ();
Fields + = "like '% ";
Fields + = Item. Value. ToString ();
Fields + = "% '";
Count ++;
}
Fields + = "";
String SqlString = "select * from" + XTableName + "where" + Fields;
Open ();
SqlDataAdapter Adapter = new SqlDataAdapter (SqlString, Connection );
DataSet Ds = new DataSet ();
Adapter. Fill (Ds );
Close ();
Return Ds;
}
// Private method to obtain a SqlCommand used to call the Stored Procedure
// Input:
// ProcName-name of the stored procedure
// Params-parameter table used to call a stored procedure
Supports different database names in the same database (for example, two databases in SQL Server: Test1 and Test2)
Public static class DBHelper
{
Public static connString = "connection 1 ";
Public static DataSet Query (string SQL)
{
.................
Return ds;
}
.................
}
Public class DBHelperA
{
Public connString = "connection 2 ";
Public DataSet Query (string SQL)
{
.................
Return ds;
}
.................
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
