C#.net How to access the SQL Server database

C#.net How to access the SQL Server database

1. Import namespaces

Using System.Data.SqlClient; Connect to SQL Server database private

2. Create a connection

SqlConnection lo_conn = New SqlConnection ("server= server name or ip;database= database name; uid= user name; pwd= password");

3, open the connection, the 2nd step does not really connect the database

Lo_conn. Open (); Really connect to the database

4. Send SQL commands to the database to use SqlCommand:

SqlCommand lo_cmd = new SqlCommand (); To create a Command object

Lo_cmd.commandtext = "Here is the SQL statement"; Write SQL statements

Lo_cmd. Connection = Lo_con; Specifies the connection object, which is created above

5. Handling SQL commands or returning result sets

Lo_cmd. ExecuteNonQuery (); This simply executes the SQL command, does not return a result set, and is used to build tables, batch updates, etc. that do not need to return results.

SqlDataReader Lo_reader = Lo_cmd. ExecuteReader ();//return result set

6. Back to the result set as a dataset

SqlDataAdapter dbadapter = new SqlDataAdapter (Lo_cmd); Note the separation from the upper section

DataSet ds = new DataSet (); To create a DataSet object

Dbadapter.fill (DS); Populates the dataset with the returned result set, which can be used to manipulate the data by the control DataBind

7. Close the connection

Lo_conn. Close ();

C#. NET Operational Database General class (MS SQL Server)

The following is a generic class of C # Operations MS SQL Server database that allows you to perform any operation on the database, including executing SQL statements and executing stored procedures. The following is the detailed implementation of the process, I hope that we work together to modify the optimization. We'll show you how to use it to implement N-tier programming later.

Configure link parameters for the Web. config file

<appSettings>

<!--

CONNSTR parameter setting, case description:

(1) SQL Server database, for example "server=local;database=test;uid=sa;pwd=;"

(2) Access database, e.g. "DATA\EX.MDB; User id= ' admin '; Jet oledb:database password= ' admin ';

-

<add key= "ConnStr" value= "server=127.0.0.1;database=dbname;uid=sa;pwd=;"/>

</appSettings>

C # code

Using System;

Using System.Data;

Using System.Data.SqlClient;

Namespace Com.LXJ.Database

{

<summary>

A summary description of the conndb.

</summary>

public class Conndb

{

protected SqlConnection Connection;

private string connectionString;

<summary>

Default constructor

</summary>

Public Conndb ()

{

String connstr;

ConnStr = system.configuration.configurationsettings.appsettings["ConnStr"]. ToString ();

connectionString = ConnStr;

Connection = new SqlConnection (connectionString);

}

<summary>

Constructors with parameters

</summary>

<param name= "newconnectionstring" > Database join String </param>

Public conndb (String newconnectionstring)

{

connectionString = newconnectionstring;

Connection = new SqlConnection (connectionString);

}

<summary>

Completing the instantiation of the SqlCommand object

</summary>

<param name= "Storedprocname" ></param>

<param name= "Parameters" ></param>

<returns></returns>

Private SqlCommand Buildcommand (string storedprocname,idataparameter[] parameters)

{

SqlCommand command = Buildquerycommand (storedprocname,parameters);

Command. Parameters.Add (New SqlParameter ("ReturnValue", sqldbtype.int,4,parameterdirection.returnvalue,false,0,0,string. Empty,datarowversion.default,null));

return command;

}

<summary>

Create a new SQL command object (stored procedure)

</summary>

<param name= "Storedprocname" ></param>

<param name= "Parameters" ></param>

<returns></returns>

Private SqlCommand Buildquerycommand (string storedprocname,idataparameter[] parameters)

{

SqlCommand command = new SqlCommand (storedprocname,connection);

Command.commandtype = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)

{

Command. Parameters.Add (parameter);

}

return command;

}

<summary>

Execute stored procedure, no return value

</summary>

<param name= "Storedprocname" ></param>

<param name= "Parameters" ></param>

public void Executeprocedure (string storedprocname,idataparameter[] parameters)

{

Connection.Open ();

SqlCommand command;

Command=buildquerycommand (storedprocname,parameters);

Command. ExecuteNonQuery ();

Connection.close ();

}

<summary>

Executes the stored procedure, returning the number of rows affected by the execution operation

</summary>

<param name= "Storedprocname" ></param>

<param name= "Parameters" ></param>

<param name= "rowsaffected" ></param>

<returns></returns>

public int runprocedure (string storedprocname,idataparameter[] parameters,out int rowsaffected)

{

int result;

Connection.Open ();

SqlCommand command = Buildcommand (storedprocname,parameters);

rowsaffected = command. ExecuteNonQuery ();

result = (int) command. parameters["ReturnValue"]. Value;

Connection.close ();

C # Connecting SQL database, common connection string explanation, common database operation methods

2009-06-15 12:45:47