Microsoft Application Blocks for .NET学习笔记

技术2022-05-11 110

不管怎么说Microsoft Application Blocks for .NET是一个好东东,至少对我这种使用数据库经验很少的人来说,不得不说是一个福音,对数据库只要专注一下存储过程就行了.同时为.net开发做了一个好样式,尽管微软说这不是标准.

首先,可以从它的图开始

显然对于象我这样只能设计点单位小的数据库系统的人来说,条件有限,(一般只用access)sql数据库使用很少(只是自已机子上有个桌面版),连存储过程都用得很少,所以用大量的时间去写数据库操作语言,还要结合到程序中,而且常常重复工作,真是不爽,,这个东东很好.

它提供了5个方法,ExecuteNonQuery、ExecuteDataset、ExecuteReader、ExecuteScalar 和 ExecuteXmlReader。而且都是static 类的,这让人想起form程序中的程序入口也是static的,也就是说这只能有一个实例在内存中(或者说不用生成实例的,可直接可以使用的).

问题:对于不同的程序和web程序是不是在内存中也只有一个实例呢?(要实验一下,特别一个form程序时,另一个是web程序时,是不是只有一个呢? 当然前提是要将application blocks 生成DLL文件,然后进行引用.

我现在直接将application blocks引用到项目中,不知生成web程序后,和引用DLL是不是一样呢?

(1月10日,必须引用才能使用,把application blocks for .net原程序引入项目中后,进行编译就生成了microsoft applicationBlocks data.dll.然后在自已的程序中引用一下,就行,

通过 using Microsoft.ApplicationBlocks.Data就行了;其实原程序只是给你看一看调用的对象而已.)

对于这五个方法,每个方法都利用重载方式进行定义,方便了大家的使用,重载的方法都是如下形式:

[C#]Execute* (SqlConnection connection, CommandType commandType, string commandText)Execute* (SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)Execute* (SqlConnection connection, string spName, params object[] parameterValues)Execute* (SqlConnection connection, CommandType commandType, string commandText)Execute* (SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)Execute* (SqlConnection connection, string spName, params object[] parameterValues)

除此之外,还定义了以下重载函数(除ExectuteXmlReader之外)

[C#]Execute* (string connectionString, CommandType commandType, string commandText)Execute* (string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)Execute* (string connectionString, string spName, params object[] parameterValues)主要是直接提供了连接串,不用再用对象,这种方式提高了效率..

ExecuteNonQuery主要是用于查询,返回是影响的行数.可以利用存储过程中的参数传递.来得到想要的值.

每个方法要注意返回的类型:

如果要dataset 请用ExecuteDataset

eg;

一个存储过程：ALTER PROCEDURE dbo.GetExpertDataASSET NOCOUNT ON;SELECT ID, NAME AS 姓名, SEX AS 性别, ZHICHEN AS 职称, ZHIWU AS 职务, LANGU1 AS 外语, LAGU_LEVEL1 AS 外语水平, GRAMMER AS 学位, KN_LEVEL AS 水平, EXPER AS 专业, EXPER_NOW AS 现从事专业, EXPERT AS 专长 FROM EXPERTreturn 0

程序中调用方式：

private void BindGrid() { //DataGrid1.DataSource = CreateDataSource(); DataSet dsData = new DataSet(); dsData = SqlHelper.ExecuteDataset(sqlConnection1.ConnectionString, CommandType.StoredProcedure, "GetExpertData"); DataGrid1.DataSource=dsData; DataGrid1.DataMember="Table"; DataGrid1.DataBind(); //ShowStats();

显然，直接对SqlHelper.ExecuteDataset进行了直接调用，返回到一个新定义的dataset中，其实就是一个指向dataset的对象．然后对datagrid1的dataSource和dataMember进行付值后，就行了，奇怪的是在dataset中生成的表竟名为＂Table";要是在一个dataset中多生成几个表，又该怎么做呢？

如果返回某个记录,可用ExcuteReader

一个错误让人思考：

源错误:

行 507：行 508： //fill the DataSet using default values for DataTable names, etc. 行 509： da.Fill(ds); 行 510：行 511： // detach the SqlParameters from the command object, so they can be used again. 为什么在microsoft applicationBlocks.Data.dll中还会有//fill....这样的内容，这个IL连说明也在里面吗？显然，这个dll.唯一和我们asp.net　webform就是在名字空间后定义的类了．一个是public class WebForm1 : System.Web.UI.Page 另一个就是public sealed class SqlHelper它们都是由项目出现的．当然，微软在后面做了很多的．

好了这么久有开始加一点吧,2月6日又开始做一点.现在将利用这个dll来对数据库记录进行修改、插入

先看存储过程吧，修改过程ExpertUpdata

ALTER PROCEDURE dbo.ExpertUpdata

( @EXPERTId int , @NAME nvarchar(50), @SEX SmallInt, @ZHICHEN nvarchar(50), @ZHIWU nvarchar(50), @LANGU1 nvarchar(50), @LAGU_LEVEL1 nvarchar(50), @GRAMMER nvarchar(50), @KN_LEVEL nvarchar(50), @EXPER nvarchar(50), @EXPER_NOW nvarchar(50), @EXPERT ntext, @BIRTHDAY datetime, @CONTRY nvarchar(50), @DEPART nvarchar(50), @EMAIL nvarchar(50), @HAND_CALL nvarchar(50), @HOEM_ADRESS nvarchar(50), @HOEM_ZIP nvarchar(50), @HOME_CALL nvarchar(50), @IDENTI nvarchar(15), @LANGU_LEVEL2 nvarchar(50) , @LANGU2 nvarchar(50), @PLACE nvarchar(50), @PLACE_ADRESS nvarchar(50), @PLACE_CLASS SmallInt, @PLACE_PHONE1 nvarchar(50), @PLACE_PHONE2 nvarchar(50), @PLACE_ZIP nvarchar(50), @SMHAND_CALL nvarchar(50), @TELE nvarchar(50) )

AS /* SET NOCOUNT ON */ UPDATE EXPERT SET NAME = @NAME, SEX = @SEX, BIRTHDAY = @BIRTHDAY, HOME_CALL = @HOME_CALL, SMHAND_CALL = @SMHAND_CALL, HAND_CALL = @HAND_CALL, EMAIL = @EMAIL, IDENTI = @IDENTI, HOEM_ADRESS = @HOEM_ADRESS, HOEM_ZIP = @HOEM_ZIP, PLACE = @PLACE, PLACE_CLASS = @PLACE_CLASS, PLACE_ADRESS = @PLACE_ADRESS, PLACE_ZIP = @PLACE_ZIP, DEPART = @DEPART, ZHIWU = @ZHIWU, ZHICHEN = @ZHICHEN, PLACE_PHONE1 = @PLACE_PHONE1, PLACE_PHONE2 = @PLACE_PHONE2, TELE = @TELE, KN_LEVEL = @KN_LEVEL, GRAMMER = @GRAMMER, CONTRY = @CONTRY, LANGU1 = @LANGU1, LAGU_LEVEL1 = @LAGU_LEVEL1, LANGU2 = @LANGU2, LANGU_LEVEL2 = @LANGU_LEVEL2, EXPER = @EXPER, EXPER_NOW = @EXPER_NOW, EXPERT = @EXPERT WHERE (ID = @EXPERTID) RETURN

调用过程

SqlParameter[] sqlPara=new SqlParameter[31];//主要是定义一个参数的数组注意必须定义维数，否则要产生参数未定义的错误。 sqlPara[0] = new SqlParameter("@EXPERTId", SqlDbType.Int ); sqlPara[0].Value=Int16.Parse(Request.Params["Id_value"]);

sqlPara[1] = new SqlParameter("@NAME", SqlDbType.NVarChar,50 ); sqlPara[1].Value= NameBox.Text;

sqlPara[2] = new SqlParameter("@SEX", SqlDbType.Int ); sqlPara[2].Value= SexList.SelectedIndex;

sqlPara[3] = new SqlParameter("@ZHICHEN", SqlDbType.NVarChar,50 ); sqlPara[3].Value= ZhichenBox.Text;

sqlPara[4] = new SqlParameter("@ZHIWU", SqlDbType.NVarChar,50 ); sqlPara[4].Value= ZhiwuBox.Text;

sqlPara[5] = new SqlParameter("@LANGU1", SqlDbType.NVarChar,50 ); sqlPara[5].Value= Lang1.Text;

sqlPara[6] = new SqlParameter("@LAGU_LEVEL1", SqlDbType.NVarChar,50 ); sqlPara[6].Value = LangLevel1.Text;

sqlPara[7] = new SqlParameter("@GRAMMER", SqlDbType.NVarChar,50 ); sqlPara[7].Value= GramBox.Text;

sqlPara[8] = new SqlParameter("@KN_LEVEL", SqlDbType.NVarChar,50 ); sqlPara[8].Value= LevelBox.Text;

sqlPara[9] = new SqlParameter("@EXPER", SqlDbType.NVarChar,50 ); sqlPara[9].Value= ExperBox.Text;

sqlPara[10] = new SqlParameter("@EXPER_NOW", SqlDbType.NVarChar,50 ); sqlPara[10].Value= AreaBox.Text;

sqlPara[11] = new SqlParameter("@EXPERT", SqlDbType.NText ); sqlPara[11].Value = ExpecalBox.Text;

sqlPara[12] = new SqlParameter("@BIRTHDAY", SqlDbType.SmallDateTime ); sqlPara[12].Value = BirthBox.Text;

sqlPara[13] = new SqlParameter("@CONTRY", SqlDbType.NVarChar,50 ); sqlPara[13].Value= CountryBox.Text ;

sqlPara[14] = new SqlParameter("@DEPART", SqlDbType.NVarChar,50 ); sqlPara[14].Value= DepartBox.Text;

sqlPara[15] = new SqlParameter("@EMAIL", SqlDbType.NVarChar ,50); sqlPara[15].Value= EmailBox.Text;

sqlPara[16] = new SqlParameter("@HAND_CALL", SqlDbType.NVarChar,50 ); sqlPara[16].Value= Handcall.Text;

sqlPara[17] = new SqlParameter("@HOEM_ADRESS", SqlDbType.NVarChar,50 ); sqlPara[17].Value= AddressBox.Text;

sqlPara[18] = new SqlParameter("@HOEM_ZIP", SqlDbType.NVarChar,50 ); sqlPara[18].Value= HomezipBox.Text;

sqlPara[19] = new SqlParameter("@HOME_CALL", SqlDbType.NVarChar,50 ); sqlPara[19].Value= Homecall.Text;

sqlPara[20] = new SqlParameter("@IDENTI", SqlDbType.NVarChar,15 ); sqlPara[20].Value= IdentBox.Text;

sqlPara[21] = new SqlParameter("@LANGU_LEVEL2", SqlDbType.NVarChar ,50); sqlPara[21].Value= LangLevel2.Text;

sqlPara[22] = new SqlParameter("@LANGU2", SqlDbType.NVarChar ,50); sqlPara[22].Value= Lang2.Text;

sqlPara[23] = new SqlParameter("@PLACE", SqlDbType.NVarChar ,50); sqlPara[23].Value= CopBox.Text;

sqlPara[24] = new SqlParameter("@PLACE_ADRESS", SqlDbType.NVarChar ,50); sqlPara[24].Value= CopaddressBox.Text;

sqlPara[25] = new SqlParameter("@PLACE_CLASS", SqlDbType.SmallInt ); sqlPara[25].Value= PlaceList.SelectedIndex;

sqlPara[26] = new SqlParameter("@PLACE_PHONE1", SqlDbType.NVarChar,50 ); sqlPara[26].Value= PlacecallBox1.Text;

sqlPara[27] = new SqlParameter("@PLACE_PHONE2", SqlDbType.NVarChar,50 ); sqlPara[27].Value = PlacecallBox2.Text;

sqlPara[28] = new SqlParameter("@PLACE_ZIP", SqlDbType.NVarChar ,50); sqlPara[28].Value = PlacezipBox.Text;

sqlPara[29] = new SqlParameter("@SMHAND_CALL", SqlDbType.NVarChar,50 ); sqlPara[29].Value= Smallcall.Text;

sqlPara[30] = new SqlParameter("@TELE", SqlDbType.NVarChar ,50); sqlPara[30].Value= FaxBox.Text;

SqlHelper.ExecuteNonQuery(sqlConnection1.ConnectionString,CommandType.StoredProcedure, "ExpertUpdata", sqlPara);

以上没什么好说的，只是没有进行错误处理，注意应该用try

现在是关于插入的过程InsetExpert

ALTER PROCEDURE dbo.InsetExpert

( @ExpertId int OUTPUT, @NAME nvarchar(50), @SEX SmallInt, @ZHICHEN nvarchar(50), @ZHIWU nvarchar(50), @LANGU1 nvarchar(50), @LAGU_LEVEL1 nvarchar(50), @GRAMMER nvarchar(50), @KN_LEVEL nvarchar(50), @EXPER nvarchar(50), @EXPER_NOW nvarchar(50), @EXPERT ntext, @BIRTHDAY nvarchar(50), @CONTRY nvarchar(50), @DEPART nvarchar(50), @EMAIL nvarchar(50), @HAND_CALL nvarchar(50), @HOEM_ADRESS nvarchar(50), @HOEM_ZIP nvarchar(50), @HOME_CALL nvarchar(50), @IDENTI nvarchar(15), @LANGU_LEVEL2 nvarchar(50) , @LANGU2 nvarchar(50), @PLACE nvarchar(50), @PLACE_ADRESS nvarchar(50), @PLACE_CLASS SmallInt, @PLACE_PHONE1 nvarchar(50), @PLACE_PHONE2 nvarchar(50), @PLACE_ZIP nvarchar(50), @SMHAND_CALL nvarchar(50), @TELE nvarchar(50) )

AS /* SET NOCOUNT ON */ INSERT INTO EXPERT ( NAME, SEX, ZHICHEN, ZHIWU, LANGU1, LAGU_LEVEL1, GRAMMER, KN_LEVEL, EXPER, EXPER_NOW, EXPERT, BIRTHDAY, CONTRY, DEPART, EMAIL, HAND_CALL, HOEM_ADRESS, HOEM_ZIP, HOME_CALL, IDENTI, LANGU_LEVEL2, LANGU2, PLACE, PLACE_ADRESS, PLACE_CLASS, PLACE_PHONE1, PLACE_PHONE2, PLACE_ZIP, SMHAND_CALL, TELE ) VALUES ( @NAME, @SEX, @ZHICHEN, @ZHIWU, @LANGU1, @LAGU_LEVEL1, @GRAMMER, @KN_LEVEL, @EXPER, @EXPER_NOW, @EXPERT, @BIRTHDAY, @CONTRY, @DEPART, @EMAIL, @HAND_CALL, @HOEM_ADRESS, @HOEM_ZIP, @HOME_CALL, @IDENTI, @LANGU_LEVEL2, @LANGU2, @PLACE, @PLACE_ADRESS, @PLACE_CLASS, @PLACE_PHONE1, @PLACE_PHONE2, @PLACE_ZIP, @SMHAND_CALL, @TELE ) SELECT @ExpertId = @@Identity RETURN

当然最后一句是为了返回增加的ID号的。

调用过程：

SqlParameter[] sqlPara=new SqlParameter[31]; sqlPara[0] = new SqlParameter("@EXPERTId", SqlDbType.Int ); sqlPara[0].Direction = ParameterDirection.Output;