ASP tutorial. NET Database Tutorial Connection classes
<%@ Page language= "C #" contenttype= "text/html" responseencoding= "gb2312"%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>
<title>asp.net Tutorial Database Connection class </title>
<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, instantiating an argument used to invoke a stored procedure
Input:
ParamName-Parameter name
DbType-Parameter type
Size-Parameter size
Direction-Transfer Direction
Value-Values
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, instantiating an input parameter used to invoke a stored procedure
Input:
ParamName-Parameter name
DbType-Parameter type
Size-Parameter size
Value-Values
Public SqlParameter Makeinparam (string paramname, SqlDbType DbType, int Size, Object Value)
{
Return Makeparam (ParamName, DbType, Size, ParameterDirection.Input, Value);
}
Public method, calling a stored procedure (without parameters)
Input:
ProcName Stored Procedure Name
Output:
Returns the number of rows affected by the update, Insert, delete operation, and other 1
public int Runproc (string procname)
{
int Count =-1;
SqlCommand CMD = CreateCommand (procname, NULL);
Count = Cmd.executenonquery ();
Close ();
return Count;
}
Public method, calling stored procedures (with parameters)
Input:
procname-Stored Procedure name
Params-parameter table used to invoke a stored procedure
Output:
Returns the number of rows affected by the update, Insert, delete operation, and other 1
public int Runproc (string procname, sqlparameter[] Params)
{
int Count =-1;
SqlCommand CMD = CreateCommand (procname, Params);
Count = Cmd.executenonquery ();
Close ();
return Count;
}
Public method, calling a stored procedure (without parameters)
Input:
ProcName Stored Procedure Name
Output:
Returns the execution result to SqlDataReader
Note: Call the Sqldatareader.close () method with the following idea
Public SqlDataReader Runprocgetreader (string procname)
{
SqlCommand CMD = CreateCommand (procname, NULL);
Return Cmd.executereader (System.Data.CommandBehavior.CloseConnection);
}
Public method, calling a stored procedure (without parameters)
Input:
ProcName Stored Procedure Name
Output:
Returns the execution result to SqlDataReader
Note: Call the Sqldatareader.close () method with the following idea
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 ());
}
Public method, calling stored procedures (with parameters)
Input:
procname-Stored Procedure name
Params-parameters required for stored procedures
Output:
Returns the execution result to SqlDataReader
Note: Call the Sqldatareader.close () method with the following idea
Public SqlDataReader Runprocgetreader (string procname, sqlparameter[] Params)
{
SqlCommand CMD = CreateCommand (procname, Params);
Return Cmd.executereader (System.Data.CommandBehavior.CloseConnection);
}
Public method, calling stored procedures (with parameters)
Input:
procname-Stored Procedure name
Params-parameters required for stored procedures
Output:
Returns the execution result to SqlDataReader
Note: Call the Sqldatareader.close () method with the following idea
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);
}
Public method, calling a stored procedure (without parameters)
Input:
ProcName Stored Procedure Name
Output:
Returns the execution result to the 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;
}
A public method that returns a result dataset 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 methods, inserting 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, according to the SQL statement, inserts a record and returns the generated ID number
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>
Database Connection Class Two
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, returns 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, updating some records in the datasheet xtablename according to Xwhere
xtablename--Table Name
xht--hash table, key is field name, value is 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, obtaining a SqlCommand to invoke the stored procedure
Input:
procname-Stored Procedure name
Params-parameter table used to invoke a stored procedure
Support for different database names in the same database (for example, two databases in SQL Server: TEST1,TEST2)
public static Class DBHelper
{
public static connstring= "Connection one";
public static DataSet Query (String sql)
{
.................
return DS;
}
.................
}
public class Dbhelpera
{
Public connstring = "Connection Two";
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
