建立:
建立資料庫mydb.mdf、添加表:更新資料庫;
右擊資料庫,選擇屬性,複制連接配接字元串;(這裡之前做過了,就簡單點過不做細節。
點選這裡可以看詳細步驟截圖參考_《用ASP.NET做一個簡單的資料流動展示》)
到web.config:
注意相對路徑|DataDirectory|......<connectionStrings>
<add name="DefaultConnection"
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=aspnet-manages-c703b6e8-d35a-4082-aadf-60f5ad784980;AttachDbFilename=|DataDirectory|\aspnet-manages-c703b6e8-d35a-4082-aadf-60f5ad784980.mdf;Integrated Security=SSPI"
providerName="System.Data.SqlClient"/>
<add name ="connstr" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\mydb.mdf;Integrated Security=True"/>
</connectionStrings>
删除,重建default視窗檔案;
設計,插入表:
設計:
頂行合并;
輸入文字;
頂行内容居中;
二列添加textbox;
改id:
Name_TextBox
Cate_TextBox
Price_TextBox
Time_TextBox
Address_TextBox
Contact_TextBox
合并末行,加個button,修改text,加個GridView:
建立一個common類:
右擊App_Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// common 的摘要說明
/// </summary>
public class common
{
public common()
{
//
// TODO: 在此處添加構造函數邏輯
//
}
public static SqlConnection myconn()
{
string connstr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
SqlConnection myconn = new SqlConnection(connstr);
return myconn;
}
}
回來default視窗,輕按兩下設計界面裡邊入庫按鈕,開始編寫邏輯:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
displayDB();
}
protected void Button1_Click(object sender, EventArgs e)
{
insertDB();
displayDB();
}
//套路:1.connstr-sqlconn-open打開資料庫
//2.cmdstr-sqlcmd(cmdstr,conn)-cmd.ex執行資料庫操作,或者其他;
//3.關閉
protected void insertDB()
{
SqlConnection myconn = common.myconn();
myconn.Open();
double Pricevalue = Convert.ToDouble(Price_TextBox.Text.Trim());
string cmdstr = @"insert into Tproduct(Fname,Fcategory,Fprice,Ftime,Faddress,Fcontactname)
values('" + Name_TextBox.Text + "' , '" + Cate_TextBox.Text + "' , " + Pricevalue + " , '" + Time_TextBox.Text + "' , '" + Address_TextBox.Text + "' , '" + Contact_TextBox.Text + "' )";
SqlCommand mycmd = new SqlCommand(cmdstr,myconn);
mycmd.ExecuteNonQuery();
mycmd.Dispose();
myconn.Close();
Response.Write("<script>alert('入庫成功')</script>");
}
protected void displayDB()
{
string connstr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
SqlConnection myconn = new SqlConnection(connstr);
myconn.Open();
string cmdstr = @"select * from Tproduct";
SqlDataAdapter myda = new SqlDataAdapter(cmdstr,myconn);//查出
DataSet myds = new DataSet();//轉型
myda.Fill(myds);
GridView1.DataSource = myds;//賦能控件
GridView1.DataKeyNames = new string[] { "id" };
GridView1.DataBind();
myda.Dispose();
myds.Dispose();
myconn.Close();
}
}
如圖,插入和展示就完成了:
現在增加查找功能:
末行配置多三個控件:
分别添加點選邏輯即可:
protected void jqFind_Button_Click(object sender, EventArgs e)
{
SqlConnection myconn = common.myconn();
myconn.Open();
string cmdstr = "select *from Tproduct where Fname='" + Find_TextBox.Text.Trim() + "'";//單引号雙引号括起來
SqlDataAdapter myda = new SqlDataAdapter(cmdstr, myconn);
DataSet myds = new DataSet();
myda.Fill(myds);
int rowNum = myds.Tables[0].Rows.Count;
int columnNum = myds.Tables[0].Columns.Count;
DataTable usingTable = myds.Tables[0];
if (rowNum == 0)
{
usingTable = usingTable.Clone();//克隆
usingTable.Rows.Add(usingTable.NewRow());//加新行
GridView1.DataSource = usingTable;//賦能
GridView1.Rows[0].Cells.Clear();//清空
GridView1.Rows[0].Cells.Add(new TableCell());//加新格
GridView1.Rows[0].Cells[0].Text = "無相關記錄";//text
GridView1.Rows[0].Cells[0].ColumnSpan = columnNum;
}
else
{
GridView1.DataSource = myds;
GridView1.DataBind();
}
myds.Dispose();
myda.Dispose();
myconn.Close();
}
protected void mhFind_Button_Click(object sender, EventArgs e)
{
SqlConnection myconn = common.myconn();
myconn.Open();
string cmdstr = "select *from Tproduct where Fname like '%"+Find_TextBox.Text.Trim()+"%'";
SqlDataAdapter myda = new SqlDataAdapter(cmdstr, myconn);
DataSet myds = new DataSet();
myda.Fill(myds);
GridView1.DataSource = myds;
GridView1.DataBind();
myds.Dispose();
myda.Dispose();
myconn.Close();
}
效果:
精确查找
模糊查找