新一代数据访问工具(二)--SQLDataSource
2007年09月04日 星期二 09:01
新一代数据访问工具(二)--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; |