ADO.NET数据访问技术

 

从这里一直往下看

 

ADO.NET说白了就是.NET框架中用来访问和操作数据源的框架,其内的核心类库是System.Data.dll(我们常用的datatable与dataset就是位于其内的System.Data命名空间内),我们通过Reflecter工具或在VS里会发现.NET3.5里的System.Data.dll的版本还是2.0的。但ADO.NET3.5里多了一些其他类库如System.Linq.dll 等。

 

在初步了解了ADO.NET之后,我们来了解其内的不同的数据提供程序(就是位于System.Data.dll里的不同命名空间里的某些类的集合)。ADO.NET中主要包括两种数据提供程序1.我们常用的SqlServer数据提供程序(位于System.Data.SqlClient,主要操作SQLServer数据库)2.Ole Db数据提供程序(位于System.Data.OleDb,主要操作任何与OLE DB兼容的数据库,关于OLE DB)。   更多关于数据提供(驱动)程序

 

在初步了解了ADO.NET内的不同的数据提供程序后,我们来了解这些数据提供程序内用来实现访问和操作数据库里的数据的类。首先我们先来了解一下我们用应用程序访问和操作数据库的大体过程:

1.通过Connection类(Sql Server数据提供程序里是SqlConnection类,Ole Db数据提供程序里是OleDbConnection类)的对象和数据库建立连接。

2.根据Command类(Sql Server数据提供程序里是SqlCommand类,Ole Db数据提供程序里是OleDbCommand类)的对象的不同方法操作数据库,将数据库返回的数据付给int类型的变量(如果执行的是增删改)付给DataReader类的对象(SqlDataReader,OleDbDataReader)(如果执行的是查询)或使用DataAdapter类(SqlDataAdapter,OleDbDataAdapter)的对象执行Command类的对象的增删改或查询。

 

3.可以将DataReader或DataAdapter类的对象获得的数据保存到Datatable或Dataset类的对象里,这时即便断开数据库的链接也可以访问Datatable或Dataset类的对象里的数据。

 

不过通过Ole Db数据提供程序访问和操作SQLServer数据库时连接字符串有所不同,如:

服务器名 ; 服务器里的数据库 ; windows身份验证为true或SSPI,用OLEDB时必须设为SSPI,为false就是SQL Server身份验证 ; 指定数据提供程序,默认为Sql Server数据提供程序

Data Source=.\SQLEXPRESS;Initial Catalog=empolysystem;Integrated Security=SSPI;Provider=SQLOLEDB

其中SQLOLEDB是数据驱动程序名,用来用OLEDB方式操作SQLServer数据库

其他有Microsoft.Jet.OLEDB.4.0,用来用OLEDB方式操作Access数据库

这些数据驱动程序都位于System.Data.OleDb命名空间内,是该命名空间内某些类的集合。

 

设置 Microsoft SQL Server 别名: 1.http://msdn.microsoft.com/zh-cn/library/ms175176.aspx   2.我觉得在SQL SERVER Configuration Manger里设置

http://www.connectionstrings.com/ 连接大全

 

现在我们来挨个了解一下这些类(以Sql Server数据提供程序为例):

1.Connection

其有两种构造函数:

 SqlConnection conn = new SqlConnection();  然后再给其ConnectionString属性赋值,ConnectionString=连接字符串;

 SqlConnection sconn = new SqlConnection(连接字符串);

这两种方法都能和数据库建立连接,通过Reflecter工具可以看出后者是在实例化运行构造函数时就给ConnectionString属性赋值了,而且只有this.ConnectionString = connectionString;这一句,再看看其方法后面还有一句:this(),将鼠标移上去看this()指的正是SqlConnection()构造函数,原来方法也可以继承啊！

2.Command

其有4种构造函数:

 SqlCommand cmd = new SqlCommand(); 然后分别给其CommandText,Connection属性赋值

 SqlCommand cmd = new SqlCommand(SQL语句或存储过程名或表名);  然后给其Connection属性赋值

 SqlCommand cmd = new SqlCommand(SQL语句或存储过程名或表名,SqlConnection对象);  

 SqlCommand cmd = new SqlCommand(SQL语句或存储过程名或表名,SqlConnection对象,SqlTransaction对象);

通过Reflecter工具可以看出Command也使用了方法继承。这些属性可以在其构造函数里直接赋值,但有些属性是必须单独赋值的,比如上面的CommandText如果我们选择了存储过程名,我们就必须单独给其CommandType属性赋值cmd.CommandType=CommandType.StoredProcedure;CommandType是枚举类型,这里表示命令类型是存储过程,CommandType.Text表示命令类型是SQL语句,是默认值。

 

关于事务Transaction(Sql Server数据提供程序里是SqlTransaction),关键的解释就是你执行的所有操作数据库的命令,比如一条或多条SQL语句,这些语句要么都执行要么都不执行,所以当希望执行SQL命令时,特别是一组命令,为保证一组命令均被执行,而不是只执行其中一部分,这时就应该选择运用事务。更多关于事务   更多关于事务

使用事务:http://www.cnblogs.com/lovecherry/archive/2005/04/11/135365.html

 

string conn = @"Data Source=.\SQLEXPRESS;Initial Catalog=empolysystem;Integrated Security=True";

            SqlConnection Conn = new SqlConnection(conn);

 SqlTransaction trans = Conn.BeginTransaction();    SqlConnection对象的BeginTransaction()方法启动事务

            Conn.Open();

SqlCommand cmd = new SqlCommand("select * from commanager", Conn,trans);   使用第4中构造函数,使用事务

            try

            {

                SqlDataReader read = cmd.ExecuteReader();

                while (read.Read())

                {

                    Response.Write("SqlServer--" + read["name"] + " " + read["tel"] + "<br/>");

                }

                read.Close();

                trans.Commit();     正常完成后提交事务

            }

            catch (Exception g)

            {

                trans.Rollback();    出错的话进行回滚,不执行任何语句

            }

            finally

            {

                Conn.Close();

            }     

上面的代码没有什么互动性,代码都是死的,如果我们要在SQL语句或存储过程中插入参数,又要提防SQL注入,就要用到SqlCommand的对象的另外一个属性Parameters,通过Reflecter工具可以看出该属性返回一个类型是SqlParameterCollection的对象(通过Reflecter工具可以看出当第一次使用Parameters属性时会实例化一个SqlParameterCollection类型的对象赋给一个字段:this._parameters = new SqlParameterCollection();等再次使用Parameters属性时会直接返回该字段,所以每次使用Parameters属性时都是对同一个SqlParameterCollection类型的对象操作),该对象保存了所有SqlCommand的对象的参数,这些参数的类型是SqlParameter,它们全都保存在了SqlParameterCollection的对象里。SqlParameter类型的参数的主要属性有ParameterName(参数名,Sql Server数据提供程序写为@**,而Ole Db数据提供程序写为?)Value(参数值)Direction(枚举类型ParameterDirection,该属性表明是为存储过程提供数据还是等待存储过程返回数据,可取值有Input(输入),InputOutput(输入输出),Output(输出),ReturnValue(返回值),默认是Input)

 

参数的使用,我一般都是使用AddWithValue()和AddRange()

cmd.Parameters.AddWithValue(); 只能一个参数一个参数的添加到SqlParameterCollection的对象里,且只能添加参数名+参数值的形式,通过Reflecter工具可以看出在AddWithValue()内部就是用Add()方法向SqlParameterCollection的对象里添加一个参数并返回return this.Add(new SqlParameter(parameterName, value));

 

 SqlParameter[] parmas = new SqlParameter[] { new SqlParameter("@**","**"),new SqlParameter("@**","**"),new SqlParameter("@**","**")};

cmd.Parameters.AddRange(parmas ); 这种方法能将一个参数数组添加到SqlParameterCollection的对象里,由于每个参数都是使用SqlParameter类实例化,所以各种形式的参数都可以添加,如参数名+参数值的形式,参数名+参数类型的形式等。而且由于其参数是一个SqlParameter[]数组,所以很方便用来在方法之间调用,如在DAL里的SQLHelper就经常使用该方法。

 

所以最好用AddRange(),而需要得到存储过程的返回值时,由于需要设置参数的Direction属性,所以单独实例化一个参数然后添加到SqlParameter[] parmas = new SqlParameter[] {}集合里比较好(还可以通过cmd.Parameters.Add()添加,不过这种又不方便在方法之间调用了),要获得返回值时可以通过该参数的引用获得,也可以通过cmd.Parameters的索引获得:

string ssconn = @"Data Source=.\SQLEXPRESS;Initial Catalog=empolysystem;Integrated Security=True";

            SqlConnection sconn = new SqlConnection(ssconn);

            sconn.Open();

            SqlTransaction trans = sconn.BeginTransaction();

            SqlCommand scom = new SqlCommand("TestOUTPUT", sconn,trans);   

            scom.CommandType = CommandType.StoredProcedure;

            SqlParameter par1 = new SqlParameter("@return", SqlDbType.Int);  单独实例化一个参数,该参数用来得到存储过程return回来的值。最好指明返回值的类型

            par1.Direction = ParameterDirection.ReturnValue;                           设置参数的Direction属性

SqlParameter par2 = new SqlParameter("@id", SqlDbType.Int); 单独实例化一个参数,该参数用来得到存储过程的OUTPUT参数的值(参数名与存储过程里对应的参数名相同)。最好指明返回值的类型

            par2.Direction = ParameterDirection.Output;                                  设置参数的Direction属性

       SqlParameter par3 = new SqlParameter("@tel", SqlDbType.Int); 参数名与存储过程里对应的参数名相同

            par3.Direction = ParameterDirection.Output;

            SqlParameter[] parmas = new SqlParameter[] { new SqlParameter("@name", "张瑞敏"),par1,par2,par3};  将这些参数然后添加到 SqlParameter[] 集合里,可忽略顺序

            scom.Parameters.AddRange(parmas);

            try

            {

                SqlDataReader sread = scom.ExecuteReader();

                while (sread.Read())

                {

                    Response.Write("SqlServer--" + sread["name"] + " " + sread["tel"] + "<br/>");

                }

                sread.Close();

                                        通过cmd.Parameters的索引获得(OUTPUT参数)返回值

                Response.Write(scom.Parameters["@return"].Value.ToString()+"<br/>");   当命令以com.ExecuteReader()的形式执行时,返回值的获得要写在read.Close();后面

           Response.Write(scom.Parameters["@output"].Value.ToString() + "<br/>"); 当命令以com.ExecuteReader()的形式执行时,OUTPUT返回值的获得也要写在read.Close();后面

            Response.Write(scom.Parameters["@tel"].Value.ToString() + "<br/>");

                trans.Commit();

            }

            catch (Exception g)

            {

                trans.Rollback();

                Response.Write(g.Message);

            }

            finally

            {

                sconn.Close();

            }   

存储过程的定义(更多存储过程的建议):

CREATE PROCEDURE TestOUTPUT

@name nvarchar(10),

@id int output,

@tel nvarchar(10) output

AS

select * from commanager where name=@name;

set @id=(select commanagerid from commanager where name=@name);

set @tel=(select tel from commanager where name=@name);

没有写return,默认return返回值为0

 

http://www.cnblogs.com/blsong/archive/2009/12/11/1621713.html|荐

http://www.cnblogs.com/tangself/archive/2009/12/07/1618615.html

 

 

3.SqlDataAdapter与DataSet(DataSet不属于数据驱动程序)

前面已经提起过SqlDataAdapter可以进行增删改查数据库(实际上是使SqlCommand执行其SQL语句或存储过程),但它最主要的作用是用于填充DataSet(通过执行SqlCommand的select的SQL语句或存储过程)。

 

首先来看一下通过SqlDataAdapter执行SqlCommand的增删改

先看其中一种构造函数.ctor(string selectCommandText, string selectConnectionString)

SqlDataAdapter adapter = new SqlDataAdapter("select * from comment", @"Data Source=.\sqlexpress;Initial Catalog=newsystem;Integrated Security=True");

这样就可以查出记录了,再接下来就可以填充到dataset里了,难道不需要SqlConnection对象和SqlCommand对象?是必须需要的。通过Reflecter工具可以看出这两个对象都是在其构造函数里实例化的。在SqlDataAdapter的构造函数里所设置的SqlCommand和SqlConnection都是为SelectCommand属性设置的,其他InsetCommand,DeleteCommand,UpdateCommand的SqlCommand和SqlConnection都不是在SqlDataAdapter的构造函数里设置的。除SelectCommand的SqlConnection外,其它**Command的SqlConnection都是必须是打开才能用(这在后面讲Fill()填充DataSet时会讲),所以如果这些**Command的SqlConnection是共用的话,最好显示的打开,用完了再显示的关闭。

SqlConnection conn=new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=empolysystem;Integrated Security=True");

conn.open();

  SqlCommand incmd = new SqlCommand();    InsertCommand的SqlCommand

 incmd.CommandText = "insert into leaveword (companyid,accounter,content) values (19,'霸气十足','IamInsert')";

  incmd.Connection = conn;

 //incmd.Connection.Open();  共用的conn在上面已经打开

            da.InsertCommand = incmd;

            da.InsertCommand.ExecuteNonQuery();

            SqlCommand decmd = new SqlCommand("delete from leaveword where content='IamInsert' and leavewordid>40");       DeleteCommand的SqlCommand

            da.DeleteCommand = decmd;

            da.DeleteCommand.Connection = conn;

            da.DeleteCommand.ExecuteNonQuery();   这样执行DeleteCommand的SqlCommand也是直接操作数据库,不知道微软为何搞来这些**Command属性,不如直接用SqlCommand好

            conn.Close();

 

再来看一下通过SqlDataAdapter填充DataSet或Datatable(要设置其SelectCommand属性才行)

 SqlConnection conn=new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=empolysystem;Integrated Security=True");

            SqlCommand secmd=new SqlCommand("select * from leaveword",conn);    用于SqlDataAdapter的SelectCommand属性的SqlCommand

            SqlDataAdapter da = new SqlDataAdapter(secmd);  前面讲了,在SqlDataAdapter的构造函数里所设置的SqlCommand和SqlConnection都是为SelectCommand属性设置的

            DataSet ds = new DataSet();

            da.FillSchema(ds, SchemaType.Mapped, "leaveword");  也可以不要这句,但如果只要Fill(),那么填充到DataSet里的表将不包括源表的主键信息就约束信息等。

            da.Fill(ds, "leaveword");    这里可以填充DataSet,也可以填充DataTable。这里是填充到了DataSet内一个名为leaveword的DataTable里,如果不指明名称,默认名称为table。

值得一提的是,我们并不需要打开或关闭conn,用Fill()时,da会自动打开SelectCommand属性的conn,等填充完了DataSet之后再关闭SelectCommand属性的conn,如果在用Fill()时已经打开了

SelectCommand属性的conn,等填充完了DataSet之后,SelectCommand属性的conn仍然继续保持打开状态。

            GridView1.DataSource = ds.Tables["leaveword"];

            GridView1.DataBind();

 

我们用SqlDataAdapter的Fill()方法将源表里的数据填充到DataSet中之后,我们就可以对DataSet中的数据进行增删改查了,更新完之后只需用SqlDataAdapter的Update()方法将DataSet中的数据更新到数据库就可以了。

  SqlDataAdapter da = new SqlDataAdapter("select * from leaveword", @"Data Source=.\sqlexpress;Initial Catalog=empolysystem;Integrated Security=True");

  这里SQL查询语句还必须至少返回一个主键列或唯一的列。如果什么都没有返回，就会产生 InvalidOperation 异常。

            SqlCommandBuilder cmdb = new SqlCommandBuilder(da);   这句很重要,SqlCommandBuilder将为我们对DataSet的修改自动生成需要的Sql语句

            DataSet ds = new DataSet();

            da.Fill(ds, "leaveword");

            DataRow newrow = ds.Tables["leaveword"].NewRow();   DataRow不能通过New的方式实例化

            newrow["companyid"] = 22;

            newrow["accounter"] = "GJ";

            newrow["content"] = "我是通过DataSet添加的";

            newrow["reply"] = "";

            ds.Tables["leaveword"].Rows.Add(newrow);                                            增

            ds.Tables["leaveword"].Rows[8].Delete();                                                删

            ds.Tables["leaveword"].Rows[9]["content"] = "呀!我上面的记录被删了!";     改

            da.Update(ds.Tables["leaveword"]);                    使用Update方法将数据更新到数据库                                   ds.Tables["leaveword"].AcceptChanges();            关于ds,dt的AcceptChanges()方法

            GridView1.DataSource = ds.Tables["leaveword"];

            GridView1.DataBind();

 

 

4.SqlDataReader与DataTable(DataTable不属于数据驱动程序)

SqlDataReader也可以将数据库中的数据读到内存当中,但该对象只能向前一条一条记录的读,由reader.Read()来读取一条记录并且判断有没有下一条记录。将读到的记录修改后是无法像DataSet那样更新回数据库的。但SqlDataReader读取和处理数据较快。

  SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=newsystem;Integrated Security=True");

          conn.Open();

          SqlCommand cmd = new SqlCommand("select * from comment", conn);

          SqlDataReader reader = cmd.ExecuteReader();

          reader.Read();    这里只是读取一条记录

          Response.Write(reader["content"].ToString());

          reader.Close();

          conn.Close();

ADO.NET2.0针对DataTable对象引入了新的数据填充方法---Load():

DataTable dt=new DataTable();

dt.Load(reader);

该方法可以接受的参数类型是任何实现了IDataReader接口的类的对象。