新一代数据访问工具(二)--SQLDataSource
一、配置连接字符串:<connectionStrings>
<add name="conn1" connectionString="server=.\sqlexpress;database=mydb;uid=sa;pwd=123" />
</connectionStrings>
二、读取连接字符串:
编程方式:
string str = System.Configuration.ConfigurationManager.ConnectionStrings["conn1"].ToString();
声明方式:
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:conn1 %>" ID="sds1" runat="server">
三、SQLDataSource的几个重要的属性:
ConnectionString:(string)与数据库连接字符串
SelectCommand:(string)执行查询的SQL语句
InsertCommand:(string)执行插入的SQL语句
UpdateCommand:(string)执行更新的SQL语句
DeleteCommand:(string)执行删除的SQL语句
ID:控件的ID,(据绑定控件一起使用)
四、SQLDataSource的几个重要方法:
Select():调用SelectCommand进行查询
Insert():调用InsertCommand进行插入
Update():调用UpdateCommand进行更新
Delete():调用DeleteCommand进行删除
五、SQLDataSource的几个重要子元素:
InsertParameters:为InsertCommand的SQL语句提供参数
UpdateParameters:为UpdateParameters的SQL语句提供参数
DeleteParameters:为DeleteParameters的SQL语句提供参数
SelectParameters:为SelectParameters的SQL语句提供参数
上面四个Parameters对象的参数:
Name:对应SQL语句中相应的参数的名称(不用加@)
ControlID:如果参数的值来自于页面控件,那此属性就对应控件的ID值
Size:参数的长度
Type:参数的类型
Direction:参数的方向(Input,Output,ReturnValue)
DefaultValue:参数没赋值的话的默认值
六、SQLDataSource以声明方式实现增删改查
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:conn1 %>"
SelectCommand="SELECT * FROM fruit "
InsertCommand="insert into fruit(ids,name,price,source,stack) values(@ids,@name,@price,@source,@stack)"
UpdateCommand = "update fruit set name=@name,price = @price,source = @source,stack = @stack where ids = @ids"
DeleteCommand = "delete from fruit where ids = @ids"
ID="SqlDataSource1" runat="server" ProviderName="System.Data.SqlClient">
<DeleteParameters>
<asp:ControlParameter ControlID="txtIds" Name="ids" />
</DeleteParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="txtIds" Name="ids" />
<asp:ControlParameter ControlID="txtName" Name="name" />
<asp:ControlParameter ControlID="txtPrice" Name="price" />
<asp:ControlParameter ControlID="txtSource" Name="source" />
<asp:ControlParameter ControlID="txtStack" Name="stack" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="txtIds" Name="ids" />
<asp:ControlParameter ControlID="txtName" Name="name" />
<asp:ControlParameter ControlID="txtPrice" Name="price" />
<asp:ControlParameter ControlID="txtSource" Name="source" />
<asp:ControlParameter ControlID="txtStack" Name="stack" />
</InsertParameters>
</asp:SqlDataSource>
七、SQLDataSource以编程方式实现增删改查
SqlDataSource sd = new SqlDataSource();
sd.ID = "sqlsource";
sd.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn1"].ToString();
sd.DataSourceMode = SqlDataSourceMode.DataSet;
sd.SelectCommand = "select * from fruit";
sd.InsertCommand = "insert into fruit(ids,name,price,source,stack) values(@ids,@name,@price,@source,@stack)";
sd.UpdateCommand = "update fruit set name = @name,price = @price,source=@source,stack=@stack where ids = @ids";
sd.DeleteCommand = "delete from fruit where ids = @ids";
sd.InsertParameters.Add("ids",TypeCode.String,txtIds.Text);
sd.InsertParameters.Add("name", TypeCode.String, txtName.Text);
sd.InsertParameters.Add("price", TypeCode.Decimal, txtPrice.Text);
sd.InsertParameters.Add("source", TypeCode.String, txtSource.Text);
sd.InsertParameters.Add("stack", TypeCode.String, txtStack.Text);
sd.UpdateParameters.Add("ids", TypeCode.String, txtIds.Text);
sd.UpdateParameters.Add("name", TypeCode.String, txtName.Text);
sd.UpdateParameters.Add("price", TypeCode.Decimal, txtPrice.Text);
sd.UpdateParameters.Add("source", TypeCode.String, txtSource.Text);
sd.UpdateParameters.Add("stack", TypeCode.String, txtStack.Text);
sd.DeleteParameters.Add("ids",TypeCode.String,txtIds.Text);
Controls.Add(sd);
this.GridView1.DataSourceID = sd.ID;