SqlHelper performs addition, deletion, modification, and query, while sqlHelper performs addition, deletion, and modification.

SqlHelper performs addition, deletion, modification, and query, while sqlHelper performs addition, deletion, and modification.

1. display the data in the database

How to Use sqlHelper: [You can download it online. You need to contact the landlord]

1. copy the file to the project and change its namespace to the current project name.

2. database connection information, user name, password, logon method, etc.

<ConnectionStrings>

<Add name = "con" connectionString = "Database = mySchool; Server = .; integrated Security = false; Uid = sa; Password = 123456; "providerName =" System. data. sqlClient "/>
</ConnectionStrings>

1 // 1. how to Use sqlhelper to get a data table and store it with a variable 2 string strsql = "select NewsId, title, newsclass, ViewCount from RNews where 1 = 1"; 3 // 2. find the database table corresponding to the SQL statement in the database. One dataset contains multiple data tables, 4 DataTable dt = SqlHelper. executeDataSetText (strsql, null ). tables [0]; 5 GridView1.DataSource = dt; 6 GridView1.DataBind ();

Html, front-end page, used to receive data pages

1 <div id = "gvRnews"> 2 <asp: GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "False"> 3 <Columns> 4 <asp: boundField DataField = "NewsId" HeaderText = "no."/> 5 <asp: BoundField DataField = "title" HeaderText = "title"/> 6 <asp: boundField DataField = "newsclass" HeaderText = "class"/> 7 <asp: boundField DataField = "ViewCount" HeaderText = ""/> 8 </Columns> 9 </asp: GridView> 10 </div>

The result is as follows:

 

2. query and display by title and category:

Some html code:

1 <div id = "gvSelect"> 2 <table> 3 <tr> 4 <td> title: </td> 5 <td> 6 <asp: textBox ID = "txtSTitle" runat = "server"> </asp: TextBox> 7 </td> 8 <td> category: </td> 9 <td> 10 <asp: DropDownList ID = "ddlSNewsClass" runat = "server"> 11 <asp: ListItem> -- select -- </asp: listItem> 12 <asp: ListItem> Home Page focus </asp: ListItem> 13 <asp: ListItem> Business Information </asp: ListItem> 14 <asp: listItem> trainee comments </asp: ListItem> 15 <asp: ListItem> FAQ </asp: ListItem> 16 </asp: dropDownList> 17 </td> 18 <td> 19 <asp: button ID = "btnSelect" runat = "server" Text = "query" OnClick = "btnSelect_Click"/> 20 </td> 21 </tr> 22 </table> 23 </div>

The background. cs code and the reason for adding where 1 = 1 is that the SQL statements under which conditions are correct are very important. The precondition for not adding is that neither of them can be empty.

1 protected void Page_Load (object sender, EventArgs e) 2 {3 BindRnews (); 4} 5 // write it in a method, call this method directly 6 public void BindRnews () 7 {8 // 1. how to Use sqlhelper to get a data table and store it with a variable 9 string strsql = GetStrSql (); 10 // 2. find the database table 11 DataTable dt = SqlHelper corresponding to the SQL statement in the database. executeDataSetText (strsql, null ). tables [0]; 12 GridView1.DataSource = dt; 13 GridView1.DataBind (); 14} 15 public string GetStrSql () 16 {17 StringBuilder Sb = new StringBuilder (); 18 sb. append ("select NewsId, title, newsclass, ViewCount from RNews where 1 = 1"); 19 // if the string content in it is not equal to null 20 if (! String. isNullOrEmpty (txtSTitle. text. trim () 21 {22 sb. append (string. format ("and title like '% {0} %'", txtSTitle. text. trim (); 23} 24 if (ddlSNewsClass. selectedIndex> 0) 25 {26 sb. append (string. format ("and NewsClass like '% {0} %'", ddlSNewsClass. selectedValue); 27} 28 return sb. toString (); 29} 30 31 protected void btnSelect_Click (object sender, EventArgs e) 32 {33 BindRnews (); 34}

Well, the query results will come out, as shown in the figure above.

 

3. Add data

1 <div> 2 <table> 3 <tr> 4 <td> title: </td> 5 <td> 6 <asp: textBox ID = "txtITitle" runat = "server"> </asp: TextBox> 7 </td> 8 <td> content: </td> 9 <td> 10 <asp: TextBox ID = "txtIText" runat = "server"> </asp: textBox> 11 </td> 12 <td> category: </td> 13 <td> 14 <asp: dropDownList ID = "ddlINewsClass" runat = "server"> 15 <asp: ListItem> -- select -- </asp: ListItem> 16 <asp: ListItem> homepage focus </asp: listItem> 17 <asp: ListItem> Business Information </asp: ListItem> 18 <asp: ListItem> Student comments </asp: ListItem> 19 <asp: listItem> FAQ </asp: ListItem> 20 </asp: DropDownList> 21 </td> 22 <td> 23 <asp: button ID = "btnAdd" runat = "server" Text = "add" OnClick = "btnAdd_Click"/> 24 </td> 25 </tr> 26 </table> 27 </div>

. Cs code:

Protected void btnAdd_Click (object sender, EventArgs e) {try {// 1. obtain the entered title and content. The selected category is string title = txtITitle. text. trim (); string text = txtIText. text. trim (); string rewsclass = ddlINewsClass. selectedIndex> 0? DdlINewsClass. selectedValue: ""; string strsql = string. format ("insert into RNews (Title, Text, CreatedTime, NewsClass) values ('{0}', '{1}', '{2 }', '{3}') ", title, text, DateTime. now. toString (), rewsclass); // run the command to check whether the request is successfully added if (SqlHelper. execteNonQueryText (strsql)> 0) {Response. write ("added successfully! ");} BindRnews ();} catch (Exception ex) {Response. Write (" contact the administrator! ");}}

Effect display:

 

4. delete data and delete data by Id

Front-end code:

1 <div> 2 <table> 3 <tr> 4 <td> ID: </td> 5 <td> 6 <asp: textBox ID = "txtdDId" runat = "server"> </asp: TextBox> 7 </td> 8 <td> 9 <asp: button ID = "btnDelect" runat = "server" Text = "delete" OnClick = "btnDelect_Click"/> 10 </td> 11 </tr> 12 </table> 13 </div>

 

. Cs code:

1 protected void btnDelect_Click (object sender, EventArgs e) 2 {3 try 4 {5 if (! String. isNullOrEmpty (txtdDId. text. trim () 6 {7 int id = Convert. toInt32 (txtdDId. text. trim (); 8 string strsql1 = string. format ("select NewsId from RNews where NewsId = '{0}'", id); 9 if (SqlHelper. exists (strsql1) 10 {11 string strsql2 = string. format ("delete RNews where NewsId = '{0}'", id); 12 if (SqlHelper. execteNonQueryText (strsql2)> 0) 13 {14 Response. write ("deleted successfully! "); 15} 16 BindRnews (); 17} 18 else19 {20 Response. Write (" Id does not exist! "); 21} 22} 23 else24 {25 Response. Write (" Enter the content! "); 26} 27} 28 catch29 {30 Response. Write (" contact the administrator! "); 31} 32}

 

Effect:

 

5. Update Data:

1 <div> 2 <table> 3 <tr> 4 <td> ID: </td> 5 <td> 6 <asp: textBox ID = "txtUId" runat = "server"> </asp: TextBox> 7 </td> 8 <td> title: </td> 9 <td> 10 <asp: TextBox ID = "txtUTitle" runat = "server"> </asp: textBox> 11 </td> 12 <td> 13 <asp: button ID = "btnU" runat = "server" Text = "Update" OnClick = "btnU_Click"/> 14 </td> 15 </tr> 16 </table> 17 </div>

. Cs

1 protected void btnU_Click (object sender, EventArgs e) 2 {3 try 4 {5 if (! String. isNullOrEmpty (txtUId. text. trim () 6 {7 int Id = Convert. toInt32 (txtUId. text. trim (); 8 string strsql1 = string. format ("select NewsId from RNews where NewsId = '{0}'", Id); 9 if (SqlHelper. exists (strsql1) 10 {11 string title = txtUTitle. text. trim (); 12 string strsql2 = string. format ("update RNews set Title = '{0}' where NewsId = '{1}'", title, Id); 13 if (SqlHelper. execteNonQueryText (strsql2)> 0) 1 4 {15 Response. Write ("Update successful! "); 16} 17 BindRnews (); 18} 19 else20 {21 Response. Write (" ID does not exist! "); 22} 23} 24} 25 catch26 {27 Response. Write (" the system is being updated. Please contact the administrator! "); 28} 29}

Effect: