ADO.NET目录汇总1

1.引用命名空间：

using System.Data;

using System.Data.SqlClient;【访问SQL Server定义的类】

2.连接字符串

string connectionString

         = "server=.;integrated security=true;database=mydb"

         = "server=.;database=mydb;user id=sa;password=123"

         = "server=.;database=mydb;uid=sa;pwd=123"

3.访问数据库

System.Data.SqlClient.SqlConnection connection

         = new SqlConnection (connectionString);

a.与数据库建立连接:connection.Open();

b.断开数据库的连接:connection.Close();或者//connection.Dispose();

4.数据库操作【命令对象和事务对象】

string sql = "insert into ...";

System.Data.SqlClient.SqlCommand command = new SqlCommand(sql, connection);

command.ExecuteNonQuery(); //返回一个影响数(int)

a.以上是隐式事务下工作

b.显式事务工作

System.Data.SqlClient.SqlTransaction trans = connection.BeginTransaction();

command.Transaction = trans;

-----》

int effectCount = command.ExecuteNonQuery();   //工作内容

《-----

->trans.Commit();

->trans.Rollback();

5.访问存储过程-传入参数-传出参数

a.传入参数

string sql = "存储过程名字";

System.Data.SqlClient.SqlCommand command = new SqlCommand(sql, connection);

command.CommandType = = CommandType.StoreProcedure;//设置存储过程

System.Data.SqlClient.SqlParameter pEnd = new SqlParameter("@end",System.Data.SqlDbType.Int);

pEnd.Value = 100;    //参数对象

command.Parameters.Add(pEnd);//加入参数

-->command.ExecuteNonQuery();

b.传出参数

System.Data.SqlClient.SqlParameter pResult = new SqlParameter("@result",System.Data.SqlDbType.Int);

pResult.Direction = ParameterDirection.Output;//参数对象

command.Parameters.Add(pResult);--OK

-->int result = (int) pResult.Value;

c.Return参数

System.Data.SqlClient.SqlParameter pReturn = new SqlParameter("@returnValue",System.Data.SqlDbType.Int);

pReturn.Direction = ParameterDirection.ReturnValue;

command.Parameters.Add(pReturn);--OK

-->int returnValue = pReturn.Value as int;

6.DataReader查询

using ( SqlConnection connection = new SqlConnect(connectionString))

{

         connection.Open();

         string sql = "select sid,sname,salias,sage from tb_students";

         SqlCommand command = new SqlCommand(sql, connection);

        

         SqlDataReader reader = command.ExecuteReader();

         while(reader.Read())        //返回boolean

         {

                   int id = reader.GetInt32(0);       //支持索引器 reader[0] eader["sid"];

                   string name = reader.GetString(1);

                   string alias = null;

                   if(!reader.IsDBNull(2))

                   {

                            salias = reader.GetString(2);

                   }

                  

                   int? age = null; //可空类型,用于值类型

                   age = reader.GetInt32(3);

                   if(age.HasValue)

                   {

                            age.value;

                   }

         }

         reader.Dispose();

}

7.读取单个的值-数据访问接口-数据访问对象

a.第一行，第一列：

object obj = command.ExecuteScalar();

System.Data.SqlClient.SqlConnection

System.Data.SqlClient.SqlCommand

System.Data.SqlClient.SqlTransaction

System.Data.SqlClient.SqlParameter

Command.ExecuteNonQuery

Command.ExecuteReader

Command.ExecuteScalar

8.数据访问对象的实现

如：scope_identity()

string connectionString = "server=.;database=mydb;integrated security=true;";

using(SqlConnection connection = new SqlConnection(connectionString))

{

         string sql = "insert into ...;select scope_identity();";

         SqlCommand command = new SqlCommand(sql, connection);

         SqlParameter pval1 = new SqlParameter("@pval1",SqlDbType.ValChar,10);

         SqlParameter pval2 = new SqlParameter("@pval2",SqlDbType.ValChar,10);

         pval1.value="111";

         pval2.value="222";

         command.Parameters.Add(pval1);

         command.Parameters.Add(pval2);

         connection.Open();

         object obj = command.ExecuteScalor(); //numeric(38,0)

}

9.DataTable

System.Data.DataTable table = new System.Data.DataTable();

System.Data.DataColumn idColumn

         = new System.Data.DataColumn("sid",System.Type.GetType("System.Int32"));

         = new System.Data.DataColumn("sid",typeof(int));

System.Data.DataColumn nameColumn = new System.Data.DataColumn("sid",typeof(string));

//表结构

table.Columns.Add(idColumn);

table.Columns.Add(nameColumn);

//主键约束

table.ParmaryKey = new DataColumn[]{ idColumn };

idColumn.AllowDBNull = false;

//idColumn.Unique

...

DataRow row = table.NewRow();

row[idColum]=1;

row[1]="Jasonlny";

table.Rows.Add(row);

foreach(DataRow dr in table.Rows)

{

         ...dr[..]

}

==========================================

DataTable table = new DataTable();

string connectionString = "server=.;database=mydb;integrated security=true;";

using(SqlConnection connection = new SqlConnection(connectionString))

{

         string sql = "select ... ...";

         SqlCommand command = new SqlCommand(sql, connection);

         connection.Open();

         using(SqlDataReader reader = command.ExecuteReader())

         ｛

                   int columnCount = reader.FieldCount;

        

                   for(int i=0; i<columnCount; i++)

                   {

                            DataColumn column = new DataColumn(reader.GetName(i), reader.GetFieldType(i));

                            table.Columns.Add(column);

                   }

                   while(reader.Read())

                   {

                            DataRow dr = table.NewRow();

                            for(int i=0; i<columnCount; i++)

                            {

                                     dr[i] = reader[i];

                            }

                            table.Rows.Add(dr);

                   }

         ｝

}

10.DataAdapter-DataSet

System.Data.DataSet dset = new System.Data.DataSet();

//dset.Tables...

string connectionString = "...";

string sql = "select ...";

System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(sql,connectionString);

adapter.Fill(dset);

System.Data.DataTable table = dset.Tables[0];

//adapter.SelectCommand...