為完成管理資訊系統課程設計,特意和小夥伴一起學了ADO.NET,參考了中國大學MOOC東華大學的資訊系統與資料庫技術,開發環境是visual studio 和 sql server 2014,寫下總結目的是将知識梳理一遍以免學了就忘,其次就是能夠給學習ADO的朋友一點點幫助。 我也是幾天學了點皮毛,寫的不好多多包涵。
一,功能界面
1.總體界面
2."插入"
點選插入後結果,會出現确定和删除鍵,點選其他鍵不會出現确定删除鍵,如下:
3."修改"
點選車次停留資訊表中選擇,内容會自動填充到文本框,可對其更改,再點選“修改”鍵,文本框會清空且下方會顯示“修改成功提示語”,如下:
4. "删除"
選擇表中要删除的記錄,文本框自動填充内容,再點選'删除:鍵,文本框清空且出現提示語
二,背景代碼
修改,删除,增加代碼都是相似的,注意存儲過程,經常會有明明在資料庫中建立了存儲過程可是運作時出現"存儲過程不存在"的情況,主要都是參數沒寫全,最好把字段全部寫上,代碼中有說明。
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Security;
public partial class test : System.Web.UI.Page
{
//先定義連接配接,操縱,資料集等
static SqlConnection cn;
static SqlDataAdapter da = new SqlDataAdapter();
static SqlCommandBuilder SqlBulider = new SqlCommandBuilder(da);
static DataSet ds = new DataSet();
// 顯示資料表 “車次停留”表
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ConnectionStringSettings settings;
settings = ConfigurationManager.ConnectionStrings["trainConnectionString"];//從配置檔案中提取連接配接字元串
if (settings != null)
{
cn = new SqlConnection(settings.ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select trainnumber as 車次,number as 站次,station as 站名,arrivaltime as 到達時間," +
"departuretime as 發車時間,stationtime as 停留時間 from seek_train";
cmd.Connection = cn; //cmd的屬性,connection和commandtext以上 也可這樣表示 Sqlcommand cmd=new sqlCommand("select trainnumber as 車次,number as 站次,station as 站名,arrivaltime as 到達時間," +
"departuretime as 發車時間,stationtime as 停留時間 from seek_train", cn)
da.SelectCommand = cmd;
cn.Open();
da.Fill(ds, "trainschedules");
cn.Close();
//綁定
GridView1.DataSource = ds.Tables["trainschedules"].DefaultView;
GridView1.DataBind();
}
}
//一開始确定和取消按鍵是不存在的
ButtonOK.Visible = false;
ButtonCancel.Visible = false;
}
//點選“修改”按鈕,修改選中記錄,更新資料集
protected void Button4_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_Updatetrainschedules"; //存儲過程,涉及SQL SERVER資料庫建立存儲過程,名字為pro_Updatetrainschedules,下一節有具體建立代碼
cmd.Connection = cn;
SqlParameter param1 = new SqlParameter("@trainnumber", SqlDbType.Char,10);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@number", SqlDbType.Int);
param2.Value = TextBox2.Text;
cmd.Parameters.Add(param2);
SqlParameter param3= new SqlParameter("@station", SqlDbType.Char,10);
param3.Value = TextBox3.Text;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("@arrivaltime", SqlDbType.NChar);
param4.Value = TextBox4.Text;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("@departuretime", SqlDbType.NChar);
param5.Value = TextBox5.Text;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter("@stationtime", SqlDbType.NChar);
param6.Value = TextBox6.Text;
cmd.Parameters.Add(param6);
cn.Open();
try
{
int i = cmd.ExecuteNonQuery();//執行存儲過程插入記錄,/變量i存放指令執行影響的記錄行數
if (i > 0)
{
Label6.Text = "提示:" + i + "條記錄修改成功";
}
}
catch (SqlException ex)//捕獲Try後的程式段執行異常
{
Label6.Text = ex.Message;//報告出錯的異常原因
}
cn.Close(); //關閉資料庫連接配接
//調用自定義過程UpdateDataBaseAndRefreshGridView(),将更新回寫資料庫
//并且重新整理GridView1和清空文本框
UpdateDataBaseAndRefreshGridView();
}
//點選“删除”按鈕,删除選中記錄,更新資料集
protected void Button5_Click(object sender, EventArgs e)
{
//建SqlCommand對象cmd并給相關屬性指派,指定指令類型和存儲過程名
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "proc_Deletetrainschedules";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
//定義和建立SqlParameter對象param,并為cmd的操作指令的參數指派,一定要注意6個參數要寫全,寫對,否則會出現"存儲過程"不存在的問題。
SqlParameter param1 = new SqlParameter("@trainnumber", SqlDbType.Char, 10);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@number", SqlDbType.Int, 10);
param2.Value = TextBox2.Text;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@station", SqlDbType.Char, 10);
param3.Value = TextBox3.Text;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("@arrivaltime", SqlDbType.NChar);
param4.Value = TextBox4.Text;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("@departuretime", SqlDbType.NChar);
param5.Value = TextBox5.Text;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter("@stationtime", SqlDbType.NChar);
param6.Value = TextBox6.Text;
cmd.Parameters.Add(param6);
cn.Open();
try
{
int i = cmd.ExecuteNonQuery();//變量i存放指令執行影響的記錄行數
if (i > 0)
{
Label6.Text = "提示:" + i + "條記錄删除成功";
}
}
catch (SqlException ex) //捕獲Try後的程式段執行異常
{
Label6.Text = ex.Message;//報告出錯的異常原因
}
cn.Close();//關閉資料庫連接配接
//調用自定義過程UpdateDataBaseAndRefreshGridView(),将更新回寫資料庫
//并且重新整理GridView1和清空文本框
UpdateDataBaseAndRefreshGridView();
}
//在GridView1選擇一條記錄,文本框顯示該記錄各項
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
//獲得GridView1的選中行,并把各項賦給各TextBox顯示
GridViewRow row = GridView1.SelectedRow;
TextBox1.Text = row.Cells[1].Text;
TextBox2.Text = row.Cells[2].Text;
TextBox3.Text = row.Cells[3].Text;
TextBox4.Text = row.Cells[4].Text;
TextBox5.Text = row.Cells[5].Text;
TextBox6.Text = row.Cells[6].Text;
}
//在GridView1翻頁時,重新整理GridView1
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataSource = ds.Tables["trainschedules"].DefaultView;
GridView1.DataBind();
}
//點選“插入”按鈕,清空文本框,顯示“确定”和“取消”按鈕
protected void Button6_Click1(object sender, EventArgs e)
{
ButtonOK.Visible = true;
ButtonCancel.Visible = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
}
//點選“确定”按鈕,添加一條新記錄,并将文本框的内容指派給該新記錄,更新資料集
protected void Button7_Click(object sender, EventArgs e)
{
//建立SqlCommand對象cmd并給相關屬性指派,指定指令類型和存儲過程名
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "proc_AddtrainRecord";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
//定義和建立SqlParameter對象param,并為cmd的操作指令的參數指派
SqlParameter param1 = new SqlParameter("@trainnumber", SqlDbType.Char, 10);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@number", SqlDbType.Int,10);
param2.Value = TextBox2.Text;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@station", SqlDbType.Char,10);
param3.Value = TextBox3.Text;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("@arrivaltime", SqlDbType.NChar);
param4.Value = TextBox4.Text;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("@departuretime", SqlDbType.NChar);
param5.Value = TextBox5.Text;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter("@stationtime", SqlDbType.NChar);
param6.Value = TextBox6.Text;
cmd.Parameters.Add(param6);
cn.Open();
try
{
int i = cmd.ExecuteNonQuery(); //變量i存放指令執行影響的記錄行數
if (i > 0)
{
Label6.Text = "提示:" + i + "條記錄插入成功";
}
}
catch (SqlException ex) //捕獲Try後的程式段執行異常
{
Label6.Text = ex.Message; //報告出錯的異常原因
}
cn.Close(); //關閉資料庫連接配接
//調用自定義過程UpdateDataBaseAndRefreshGridView(),将更新回寫資料庫
//并且重新整理GridView1和清空文本框
UpdateDataBaseAndRefreshGridView();
}
//點選“取消”按鈕,清空文本框
protected void Button8_Click(object sender, EventArgs e)
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
}
//更新回寫資料庫,并且重新整理GridView1和清空文本框
protected void UpdateDataBaseAndRefreshGridView()
{
//将更新後的ds資料集ds
da.Update(ds, "trainschedules");
//重新擷取資料源,以便通過綁定重新整理GridView1,以反映修改結果
ds.Clear();
cn.Open();
da.Fill(ds, "trainschedules");
cn.Close();
//為控件GridView1指定顯示的資料源,将資料源綁定到GridView控件
GridView1.DataSource = ds.Tables["trainschedules"].DefaultView;
GridView1.DataBind();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
}
三,資料庫存儲過程建立
在資料庫的”可程式設計性“中的”存儲過程“滑鼠右擊建立。
1.增加
USE [train] //資料庫名為train
GO
/****** Object: StoredProcedure [dbo].[proc_AddtrainRecord] Script Date: 2019/1/20 17:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_AddtrainRecord] //ALTER改成CREATE 執行後會變成ALTER
@trainnumber char(10),
@number int,
@station char(10),
@arrivaltime nchar(10),
@departuretime nchar(10),
@stationtime nchar(10)
AS
BEGIN
insert into seek_train //表名為seek_train values(@trainnumber,@number,@station,@arrivaltime,@departuretime,@stationtime)
END
2.删除
USE [train]
GO
/****** Object: StoredProcedure [dbo].[proc_Deletetrainschedules] Script Date: 2019/1/20 17:10:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Deletetrainschedules]
@trainnumber char(10) ,
@number int,
@station char(10),
@arrivaltime nchar(10),
@departuretime nchar(10),
@stationtime nchar(10)
AS
BEGIN
delete from seek_train where [email protected] and [email protected]
END
3.更新
USE [train]
GO
/****** Object: StoredProcedure [dbo].[proc_Updatetrainschedules] Script Date: 2019/1/20 17:11:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Updatetrainschedules]
@trainnumber char(10),
@number int,
@station char(10),
@arrivaltime nchar(10),
@departuretime nchar(10),
@stationtime nchar(10)
AS
UPDATE seek_train
set [email protected],[email protected],[email protected],[email protected],[email protected],[email protected]
where [email protected] and [email protected]