Several common methods of SQL

Summary of the first project
Base class: Database: Mainly defines the methods for the databases:
1. Open the Database
public static void Open ()
{("server=.\\sqlexpress;database=shengying;intergrated security=ture")
SqlConnection conn=new SqlConnection ("server=.\\sqlexpress;database= sheying;uid=sa;pwd=123456");
Conn. OPen ();
}
2. Close the database
public static void Close ()
{
if (conn!=null)
{
Conn.close ();
Conn.dispose (); Releasing resources
}
}
3. After opening the database, you can put the data into the DataTable table by querying the SQL statement, or getting the data.
public static DataTable Getdt (String strSQL)
{
DataSet Ds=new DataSet ();D Ataset is a collection of DataTable
Ds=getds (strSQL);
if (ds. TABLES.COUNT>0)
{
Return DS. TABLE[0];
}
Else
{
return null;
}

}
3.1 After opening the database, put the data into the dataset table by querying the SQL statement
public static DataSet Getds (String strSQL)
{
DataSet ds=new DataSet ();
Open ();
Sqldataadapte da=new Sqldataadapte (strsql,conn);//Get data by adapter: 1.sql Statement 2. Open the Database
Da. Fill (DS);//populate the dataset with data obtained from the adapter;
Close ();
Return ds;//to go back to the data stored in the DS

}
4. Return SqlDataReader data stream via SQL statement///To call SqlCommand
public static SqlDataReader GETSDR (String strSQL)
{
Open ();
SqlCommand com = new SqlCommand (strSQL, conn);
Sdr=com. ExecuteReader (system.Data.CommandBehavior.CloseConnection);
Return Sdr;s
}
5. Return DataView via SQL statement??????????????????????????????
public static DataView GETDV (String strSQL)
{
ds = new DataSet ();
ds = Getds (strSQL);
if (ds. Tables.count > 0)
{
Return DS. Tables[0]. DefaultView;
}
Else
{
return null;
}
}
6. Execute the SQL statement to see if the execution of the SQL statement succeeds, using the SqlCommand call ExecuteNonQuery ()
public static bool Boolrunsql (String sql)
{
OPen ();
Try
{
Cmd=new SqlCommand (Strsql,conn);
Cmd. ExecuteNonQuery ();
Close ();
Retyrn ture;
}
Else
{
return false;
}

}
7. Executes an SQL statement that returns the number of rows affected
public static int Intrunsql (string strSQL)
{
int strrun=0;
Open ();
Try
{
Com=new SqlCommand (Strsql,conn);
Strrun=com. ExecuteNonQuery ();
Close ();
return strrun;
}
Catch
{
return strrun;
}
}
8. Return the data for the first column of the first row: Comm. ExecuteScalar ()
public static string Strrunsql (String strSQL)
{
String Strrun= "";
Open ();
Try
{
Com=new SqlCommand (Strsql,conn);
Strrun=com. ExecuteScalar (). ToString ();
Close ();
return strrun;
}
Catch
{
Retyrn "";
}
}

--------------------------------------------------------------------------------------------------------------- ----------------------------

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Web;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.OleDb;

<summary>
Summary description of database
</summary>
public class Database
{
Declaration of 6
private static SqlConnection Conn;
Private static SqlDataAdapter da;//adapter
private static DataSet ds;//
private static DataTable DT;
private static SqlCommand comm;
private static SqlDataReader SDR;
Public Database ()
{
This is a class of all methods, open (close) the database
TODO: Add constructor logic here
//
}
<summary>
Open a database connection
</summary>
private static void OPen ()
{
conn = new SqlConnection ("server=.\\sqlexpress;database=shenying;integrated security=true");

conn = new SqlConnection ("server=.\\sqlexpress;database= sheying;uid=sa;pwd=123456");
Conn. Open ();
}
<summary>
To close a database connection
</summary>
private static void Close ()
{
IF (conn! = null)
{
Shut down
Conn. Close ();
Conn. Dispose ();//Release resources
}
}
<summary>
Returning a DataSet from an SQL statement
</summary>
<param name= "strSQL" >sql query statement string</param>
<returns></returns>
private static DataSet Getds (String strSQL)
{
OPen ();
Da=new SqlDataAdapter (Strsql,conn);
Ds=new DataSet ();
Da. Fill (DS);
Close ();
return DS;
}
<summary>
Returning a DataTable from a SQL statement
</summary>
<param name= "strSQL" >sql query statement string</param>
<returns></returns>
public static DataTable Getdt (string strSQL)//Get data by angry statements, put it in a DataTable, and put it in a dataset
{
ds = new DataSet ();
ds = Getds (strSQL);
if (ds. Tables.count > 0)
{
Return DS. Tables[0];
}
Else
{
return null;
}
}
<summary>
Returning SqlDataReader with SQL statements
</summary>
<param name= "SDR" >sql statement strin</param>
<returns></returns>
public static SqlDataReader GETSDR (String strSQL)
{
OPen ();
Comm = new SqlCommand (strSQL, conn);
SDR = Comm. ExecuteReader (System.Data.CommandBehavior.CloseConnection);
return SDR;
}
<summary>
Returning DataView with SQL statements
</summary>
<param name= "strSQL" >sql statement string</param>
<returns></returns>
public static DataView GETDV (String strSQL)
{
ds = new DataSet ();
ds = Getds (strSQL);
if (ds. Tables.count > 0)
{
Return DS. Tables[0]. DefaultView;
}
Else
{
return null;
}
}
<summary>
Executes the SQL statement that returns whether the successful bool is executed
</summary>
<param name= "strSQL" >sql statement </param>
<returns></returns>
public static bool Boolrunsql (string strSQL)
{
OPen ();
Try
{
No error returns True
Comm=new SqlCommand (Strsql,conn);
Comm. ExecuteNonQuery ();
Close ();
return true;
}
catch (Exception)
{
return False if there is an error
return false;
}
}
<summary>
Executes an SQL statement that returns the number of rows affected
</summary>
<param name= "strSQL" ></param>
public static int Intrunsql (string strSQL)
{

int strrun = 0;
OPen ();
Try
{
Comm = new SqlCommand (strSQL, conn);
Strrun = Comm. ExecuteNonQuery ();
Close ();
return strrun;
}
Catch
{
return strrun;
}

}
<summary>
Returns the data for the first column of the first row
</summary>
<param name= "strSQL" ></param>
<returns></returns>
public static string Strrunsql (String strSQL)
{
String strrun = "";
OPen ();
Try
{
Comm = new SqlCommand (strSQL, conn);
Strrun=comm. ExecuteScalar (). ToString ();
Close ();
return strrun;
}
Catch
{
Return "";
}
}

}

Several common methods of SQL