141019 working with SQL database in C #

Database operations: ADO

First, the name space:

Using System.Data;

Using System.Data.SqlClient; Space for data access classes that are optimized for SQL Server

System.Data.OleDB;    System.Data.ODBC; Namespace Universal data access class space.

Odbc--open database connection Open Data interconnect

Second, step:

1. Establish a Link object--build a channel to the database.

2. Open the Channel

3. Operation Database

4. Close the Channel

Iii. category and use

Link string

Method one: server=.; Database=mydb;uid=sa;pwd=123

Method Two: Menu bar "View" → "Server Explorer" → right → "add Connection" → select "Properties" → "link string" to the right of SQL Server →vs window

(a), linked objects:

SqlConnection: The class of the linked database

1. Constructor:

SqlConnection ()

SqlConnection (String connectionString)

2. Properties:

ConnectionString: String type, connection string

State: Connection Status

3. Method:

Open ()

Close ()

CreateCommand (); Generates a SqlCommand instance that accesses the database through this linked object.

4. Example

String connectionString = "server=.; Database=mydb;uid=sa;pwd=123 ";

SqlConnection conn = new SqlConnection (connectionString);

Conn. Open ();

Operation

Conn. Close ();

(ii), Command object

SqlCommand: Command object for database operations

1. Constructor:

SqlCommand (); Recommended

SqlCommand (String sql)

SqlCommand (String Sql,sqlconnection conn)

2. Properties:

CommandText: String type, SQL statement to execute, stored procedure

The Commandtype:commandtype enumeration type. commandtype.text--to execute the SQL statement (default); commandtype.storedprocedure--to execute the stored procedure.

How do I invoke a stored procedure?

1. Assign the CommandText as the name of the stored procedure.

2. Assign CommandType to CommandType.StoredProcedure

3. Use cmd. Parameters.addwithvalue () assigns a value to the stored procedure parameter.

Connection:sqlconnection type, the link channel through which the database is accessed

Parameters: Assigning or fetching SQL Server local variables in CommandText

3. Method:

ExecuteNonQuery (); The method to perform additions and deletions to return the number of rows affected.

ExecuteReader (); The method used to execute the query, returning a SqlDataReader object to execute the query.

ExecuteScalar (); Returns the first row of columns, typically used to perform statistical queries.

int count= (int) executescalar ();

4. For example:

String connectionString = "server=.; Database=mydb;uid=sa;pwd=123 ";

SqlConnection conn = new SqlConnection (connectionString);

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Cmd.commandtext = "SQL statement";

Conn. Open ();

Cmd. ExecuteNonQuery ();

Conn. Close ();

(c), reader object. A space that occupies only one piece of data in memory. Gets the data in the database. SqlDataReader

1. Constructor function

cannot be new out. The reason is that its constructor is non-public.

It has only one way to instantiate: SqlDataReader dr = cmd. ExecuteReader ();

2. Properties

. HasRows//returns BOOL data to determine if the data can be read in the reader.

3. Methods

.    Read (); Read the data into the SqlDataReader object. Returns True if the read was successful, otherwise false.

Dr.    Close (); Close the reader. When the link is closed, the reader closes with it.

dr["column name"]; Reads a column of data from the current SqlDataReader object in memory, and the data being read is of type object.

dr[index number]; Reads a column of data from the current SqlDataReader object in memory, and the data being read is of type object.

Dr. GetString (index number), Dr. Getint (index number) ...

4. For example:

String connectionString = @ "Server=.\sqlexpress;database=mydb;uid=sa;pwd=sa";

SqlConnection conn = new SqlConnection (connectionString);

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Cmd.commandtext = "SELECT * from Info";

Conn. Open ();

SqlDataReader dr = cmd. ExecuteReader ();

while (Dr. Read ())

{

Console.WriteLine (dr["Code"). ToString () +dr["Name"]. ToString ());

}

Conn. Close ();

Services-Server Manager

Boxing (Boxing)

Change data from a value type to a reference type, and transform the data from the stack space to the heap space.

Unpacking (unboxing)

Change the data from the reference type to the value type, and transform the data from the heap space to the stack space.

int n=19;

Object obj=n; Packing.

n=20;

int m= (int) obj; Unpacking.

Harm:

1, packing, Occupy space, Occupy time, run slowly.

2, unpacking, Occupy time, may appear type exception.

Exception handling

Anomalies, which are different from normal conditions, are not necessarily errors.

Try

{

Code that may appear to be abnormal;

}

[Catch[(Exception type exception object)]

{

Once an exception occurs, it will be entered here,

Processing of error messages;

}]

[finally//regardless of whether the program is working properly, and finally must be executed, often to close the link channel.

{

}]

141019 working with SQL database in C #