SqlDataAdapter sda=new SqlDataAdapter() ;
public void RunProc(string procName ,string tempTableName ,SqlParameter[] prams ,out DataSet ds)
{
SqlCommand cmd = CreateCommand(procName, prams);//方法在下面
try
{
Open();
ds=new DataSet();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection=conn;
sda.SelectCommand = cmd;
sda.Fill(ds,tempTableName);
}
catch (Exception e)
{
ds = null;
MessageBox.Show(e.ToString());
}
finally
{
cmd.Parameters.Clear();
Close();
} } private SqlCommand CreateCommand(string procName ,SqlParameter[] prams)
{
SqlCommand cmd = new SqlCommand(procName , conn);
cmd.CommandType = CommandType.StoredProcedure; if(prams != null)
{
foreach(SqlParameter parameter in prams)
{
cmd.Parameters.Add(parameter);
}
}
return cmd;
}
procName :存储过程名,g tempTableName:ds临时名 ,SqlParameter[] prams:参数
public void RunProc(string procName ,string tempTableName ,SqlParameter[] prams ,out DataSet ds)
{
SqlCommand cmd = CreateCommand(procName, prams);//方法在下面
try
{
Open();
ds=new DataSet();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
cmd.Connection=conn;
sda.SelectCommand = cmd;
sda.Fill(ds,tempTableName);
}
catch (Exception e)
{
ds = null;
MessageBox.Show(e.ToString());
}
finally
{
cmd.Parameters.Clear();
Close();
} } private SqlCommand CreateCommand(string procName ,SqlParameter[] prams)
{
SqlCommand cmd = new SqlCommand(procName , conn);
cmd.CommandType = CommandType.StoredProcedure; if(prams != null)
{
foreach(SqlParameter parameter in prams)
{
cmd.Parameters.Add(parameter);
}
}
return cmd;
}
procName :存储过程名,g tempTableName:ds临时名 ,SqlParameter[] prams:参数
{
this.Validate();
this.customersBindingSource.EndEdit();
this.customersTableAdapter.Update(this.northwindDataSet.Customers);
MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}
不过还是谢谢你
1.在数据集里面添加一个TableAdapter,会弹出TableAdapter配置向导:选择或创建连接后;选择使用已有存储过程,就OK了
2.如楼主的例子会创建一个SELECT_queryemTableAdapter,和一个SELECT_queryem(这个是DataTable)。如果数据集你取得名字叫DataSet1,
3.实例化SELECT_queryemTableAdapter、DataSet1
SELECT_queryemTableAdapter sqTA=new SELECT_queryemTableAdapter;
DataSet1 ds1=new DataSet1 ;
sqTA.Fill(ds1.SELECT_queryem,txtName,Email,Depart)//后三项是你参数的值
4.Fill就这么简单
不过由于你的存储过程有连接,SELECT_queryemTableAdapter.Updata的DeleteCommand、InsertCommand、UpdateCommand 要根据自己的逻辑弄弄
类型化数据集也就是比弱类型DataSet方便点、简单点、快一点。
3.实例化SELECT_queryemTableAdapter、DataSet1 这个过程
自己参数的值不知道在哪里设置,刚决应该在代码里面,不过又觉得如果写在代码里面那TableAdapter好像就白用了
一样。总是觉得TableAdapter哪里应该能设置进去参数,刚刚接触有点用不大明白,呵呵。可是老大要求了就必须照做
如果有时间的话,可以给我详细地说一说吗?比如实例化在哪个方法里面之类的,拜托了
sqTA.Fill需要几个参数、参数类型是什么VS给你弄好了,你也可以看看DataSet1.Designer.cs文件及DataSet1.xsd(如果你的数据集叫DataSet1)
在哪来实例化?
比如用三层架构
1.弄个类库A,把数据集丢里面
2.在A里面的数据类SelectData
///<summary>数据集</summary>
public DataSet1 ds1{ get; set; }
///<summary>Department表的TableAdapter</summary>
public DataSet1TableAdapters.SELECT_queryemTableAdapter sqTA { get; set; }//注意SELECT_queryemTableAdapter的命名空间
3.在你的表示层里面想放哪就放哪啊
using A ;
SelectData SD=new SD;
SD.sqTA.Fill(SD.ds1.SELECT_queryem,txtName,Email,Depart);
对了,台阶前辈,为什么加你没有反应呢?
1,首先我的存储过程--UserSelectProcedure
CREATE PROCEDURE UserSelectProcedure
@userid nvarchar(5),@usernm nvarchar(30),@userage nvarchar(3),@usersecid nvarchar(3),@usertel nvarchar(15),@useremail nvarchar(50),@userredt1 nvarchar(10),@userredt2 nvarchar(10),@userupdt1 nvarchar(10),@userupdt2 nvarchar(10)
AS
SELECT M_USER.USERCD as ユーザーコード, M_USER.PASSWD as パスワード, M_USER.NM as 名称, M_USER.KANA as カナ, M_USER.AGE as 年齢,M_SECTION.SECTIONNM as 部署,M_USER.TEL as 電話番号, M_USER.EMAIL as Email, M_USER.REG_DT as 作成日, M_USER.UP_DT as 更新日 FROM M_USER INNER JOIN M_SECTION ON M_USER.SECTIONCD = M_SECTION.SECTIONCD
WHERE ( (M_USER.USERCD like '%'+@userid+'' or M_USER.USERCD like '%'+@userid+'%')
and (M_USER.NM like '%'+@usernm+'%')
and (M_USER.AGE like '%'+@userage+'%' or AGE = ''+@userage+'')
and (M_USER.SECTIONCD=M_SECTION.SECTIONCD)
and (M_USER.SECTIONCD like '%'+@usersecid+'%')
and (M_USER.TEL like ''+@usertel+'%' or M_USER.TEL like '%'+@usertel+'%')
and (M_USER.EMAIL like '%'+@useremail+'%')
and ((M_USER.REG_DT between ''+@userredt1+'' and ''+@userredt2+'' or (M_USER.REG_DT >= ''+@userredt1+'' and M_USER.REG_DT like '%'+@userredt2+'%')) or (M_USER.REG_DT like '%'+@userredt1+'%' and M_USER.REG_DT like '%'+@userredt2+'%'))
and ((M_USER.UP_DT between ''+@userupdt1+'' and ''+@userupdt2+'' or (M_USER.UP_DT >=''+@userupdt1+'' and M_USER.REG_DT like '%'+@userupdt2+'%')) or (M_USER.UP_DT like '%'+@userupdt1+'%' and M_USER.UP_DT like '%'+@userupdt2+'%')))
order by M_USER.USERCD2,创建了DataSet和TableAdapter
分别命名为:
WinTESTDataSet.Users UserFill和GetUsers()
UsersTableAdapter FillByUsersSelect和GetUserDataBySelect(@userid,@usernm,@userage,@usersecid,@usertel,@useremail,@userredt1,@userredt2,@userupdt1,@userupdt)
struserid = txtuserid.Text.Replace("'", "''").Trim(); string strusernm = "";
strusernm = txtusernm.Text.Replace("'", "''").Trim(); string struserage = "";
struserage = txtuserage.Text.Replace("'", "''").Trim(); string strusersecid = "";
strusersecid = cbousersecid.SelectedValue.ToString(); string strusertel = "";
strusertel = txtusertel.Text.Replace("'", "''").Trim(); string struseremail = "";
struseremail = txtuseremail.Text.Replace("'", "''").Trim();
string strredaty1 = dtreday1.Text;
string strredaty2 = dtreday2.Text;
string strupdaty1 = dtupday1.Text;
string strupdaty2 = dtupday2.Text; this.usersTableAdapter.FillByUsersSelect(winTESTDataSet.Users,
struserid,
strusernm,
struserage,
strusersecid,
strusertel,
struseremail,
strredaty1,
strredaty2,
strupdaty1,
strupdaty2);
因为我做这个程序需要分别用C#和VB做所以这次先粘上VB的代码了(各人认为两种都一样,只是BOSS非要一个VB的所以就先做VB了,怀疑BOSS看不懂C#,呵呵) '検索
Private Sub selectworkerbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles selectworkerbtn.Click
Dim id As String = Me.txtusercd.Text
Dim nm As String = Me.txtusername.Text
Dim age As String = Me.txtuserage.Text
Dim sectioncd As String = Me.cbousersectioncd.SelectedValue.ToString()
Dim tel As String = Me.txtusertel.Text
Dim email As String = Me.txtuseremail.Text
Dim reday1 As String = Me.dtreday1.Text
Dim reday2 As String = Me.dtreday2.Text
Dim upday1 As String = Me.dtupday1.Text
Dim upday2 As String = Me.dtupday2.Text Dim temp As New Global.UserTest.TESTDataSetTableAdapters.UserSelectProcedureTableAdapter
temp.SelectGetData(id, nm, age, sectioncd, tel, email, reday1, reday2, upday1, upday2)
temp.SelectFill(Me.TESTDataSet.UserSelectProcedure, id, nm, age, sectioncd, tel, email, reday1, reday2, upday1, upday2) Me.userview.DataSource = UserSelectProcedureBindingSource If userview.DataSource Is Nothing OrElse userview.CurrentCell Is Nothing Then
MessageBox.Show("条件に合っているユーザーは見つかりません!", "確認")
userview.DataSource = UserInfoViewBindingSource
End If
End Sub
这段代码在查询按钮事件里面生效
前面的十个变量是查询时从窗体获取输入信息的分别五个textbox,一个combobox,4个datetimepicker
创建了视图的TableAdapter为UserInfoViewTableAdapter和存储过程的TableAdapter为UserSelectProceTableAdapter
temp为存储过程UserSelectProceTableAdapter的对象
userview为DataGridView的id名称
最后当查询结果无符合条件的信息返回时,DataGridView显示数据库中所有数据
今天终于把程序都做完了,可是测试的时候发现当数据达到10000行的时候TableAdapter竟然没有手动DataSet快,不排除TableAdapter设置的问题
不知道经常用TableAdapter的朋友们有没有注意过这方面,我现在正在努力完善TableAdapter的设置毕竟是刚刚开始用它,期待它的性能比手动的好
Me.TESTDataSet.EnforceConstraints = false;
Me.TESTDataSet.UserSelectProcedure.BeginLoadData();
UsersTableAdapter.Fill(Me.TESTDataSet.UserSelectProcedure,.....);
Me.TESTDataSet.UserSelectProcedure.EndLoadData();
Me.TESTDataSet.EnforceConstraints = true;
应该比非类型化快
<configuration>
<configSection>
</configSection>
<connectionStrings>
<add name="DatabaseOwner" connectionString="dbo"/>
<add name="MySchoolConnectionString" connectionString="Data Source=.;Initial Catalog=MySchool;User ID=sa" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
楼上的麻烦一下,上面的这几句代码是什么意思,我是新手请教
你问的是web.config中的用xml写的通用类的命名空间的问题:
<connectionStrings>是命名空间的基类,所有命名空间都写在有这个标记对中间;
<add name="DatabaseOwner" connectionString="dbo"/>声明一个新的命名空间;
<add name="MySchoolConnectionString" connectionString="Data Source=.;Initial Catalog=MySchool;User ID=sa" providerName="System.Data.SqlClient"/>
这段是一个完整的命名空间的描述:
System.Data.SqlClient——是在代码文件中using 的命名空间名称;
MySchoolConnectionString——是数据库连接字串的数据库名称。