首页/變量

sql server 2008，ADO.NET:C#/SQL Server

 2023-12-07 阅读 29 评论 0

摘要：1.首次要準備的(工具)是：a.Microsoft Visual Studio Ultimate 2012;b.Microsoft SQL Server Management Studio ; 2.首先創建一個（SQL Server）數據表，并且填充相應的數據： 1 USE SQL 2 GO 3 --創建數據表中的字段 4 CREATE TABLE SQL_T( 5

1.首次要準備的(工具)是：a.Microsoft Visual Studio Ultimate 2012;b.Microsoft SQL Server Management Studio ;

2.首先創建一個（SQL Server）數據表，并且填充相應的數據：

 1 USE SQL 
 2 GO
 3 --創建數據表中的字段
 4 CREATE TABLE SQL_T(
 5 [姓名] NVARCHAR(10) NOT NULL,
 6 [班級] NVARCHAR(10) NOT NULL,
 7 [學號] INT NOT NULL,
 8 [分數]DECIMAL(5,2) NOT NULL,
 9 [ID] INT IDENTITY(1,1) NOT NULL,  --標識列
10 CONSTRAINT PK_SQL_T_1 PRIMARY KEY CLUSTERED([ID]DESC) --添加主鍵約束，降序排列
11 --對索引進行一定的設置
12 WITH(
13 PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON
14 )ON[PRIMARY]
15 
16 )ON[PRIMARY]
17 GO
18 --下面寫入數據：
19 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --設置級別
20 BEGIN TRANSACTION INSERT_SQL_T_1
21 INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] )  --選擇插入數據的字段
22 VALUES('李曉峰','計算機',6080,99.56)  --寫入數據
23 COMMIT TRANSACTION INSERT_SQL_T_1   --提交
24 GO
25 
26 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --設置級別
27 BEGIN TRANSACTION INSERT_SQL_T_2
28 INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] )  --選擇插入數據的字段
29 VALUES('李異峰','會計',885,98.23)  --寫入數據
30 COMMIT TRANSACTION INSERT_SQL_T_2   --提交
31 
32 go
33 
34 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --設置級別
35 BEGIN TRANSACTION INSERT_SQL_T_3
36 INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] )  --選擇插入數據的字段
37 VALUES('曉峰','英語',5871,87.6)  --寫入數據
38 COMMIT TRANSACTION INSERT_SQL_T_3   --提交
39 
40 go
41 
42 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --設置級別
43 BEGIN TRANSACTION INSERT_SQL_T_4
44 INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] )  --選擇插入數據的字段
45 VALUES('李四','電子技術',54,65.51)  --寫入數據
46 COMMIT TRANSACTION INSERT_SQL_T_4   --提交
47 
48 go
49 
50 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --設置級別
51 BEGIN TRANSACTION INSERT_SQL_T_1
52 INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] )  --選擇插入數據的字段
53 VALUES('張三','美術',80,256.56)  --寫入數據
54 COMMIT TRANSACTION INSERT_SQL_T_1   --提交
55 go
56 
57 --下面顯示寫入的數據表
58 SELECT*  
59 FROM[SQL_T] 
60 GO

3.1.在VS2012中加載上面創建的數據表；

sql server 2008、菜單中選擇【項目】-選擇【添加數據源】-【數據庫】-【數據集】-點擊新建連接-刷新服務器名后選擇-然后選擇對應數據表的數據庫-最后點擊測試連接，如下圖：

然后點擊下一步，下一步，完成.(以上我們是用windows身份進行連接的，此外還可以使用sql server 身份進行連接。)

然后進入SQL Server對象資源管理器中查看剛剛加載的數據庫：

SQL server。接下來接可以在WindowsForms上進行操作了。

4.1.創建數據庫的連接：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 using System.Windows.Forms;
10 using System.Data.SqlClient;  //包含數據庫連接對象的命名空間
11 
12 namespace WindowsFormsApplication1
13 {
14     public partial class Form1 : Form
15     {
16         public Form1()
17         {
18             InitializeComponent();
19         }
20 
21         private void button1_Click(object sender, EventArgs e)
22         {
23             if (textBox1.Text == "")
24             { MessageBox.Show("請輸入要連接的數據庫名！"); }
25             else
26             {
27                 try
28                 {  //連接對象其中的字符串中包含了服務器名：可以是本機的ip地址，要連接的數據庫，連接的身份，，注意“Data Source=PQC-PC\\SQLEXPRESS;”是2個“\\”
29                     SqlConnection sqlconn = new SqlConnection("Data Source=PQC-PC\\SQLEXPRESS;Initial Catalog=" + textBox1.Text.Trim() + ";Integrated Security=True;Connect Timeout=15;
Encrypt=False;TrustServerCertificate=False");
30                     sqlconn.Open(); //打開連接
31                     //判斷連接的狀態：
32                     if (sqlconn.State == ConnectionState.Open)
33                     { MessageBox.Show("恭喜已經成功連接到數據庫了！"); }
34 
35                     sqlconn.Close();
36                 }
37                 catch
38                 { { MessageBox.Show("連接失敗！"); } }
39             }
40             
41 
42         }
43     }
44 }

?

4.2.1.colse()方法關閉數據庫連接同上；

sql不支持用該后端版本設計數據庫？4.2.2. 比較colse（）方法和?Dispose （）都是關閉數據庫連接的方法，區別就是：?Dispose 不僅關閉一個連接而且還要清理連接所暫用的資源，當用close關閉連接后可以用open方法再次打開，而?Dispose 卻不能，非要再次對其連接進行初始化；

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 using System.Windows.Forms;
10 using System.Data.SqlClient;  //*****
11 
12 
13 namespace WindowsFormsApplication1
14 {
15     public partial class Form2 : Form
16     {
17         public Form2()
18         {
19             InitializeComponent();
20         }
21         SqlConnection sqlconn;
22         private void button1_Click(object sender, EventArgs e)
23         {
24             if (textBox1.Text == "")
25             {
26                 errorProvider1.SetError(textBox1, "這里不能為空！");  //驗證提示輸入
27 
28             }
29             else
30             {
31                 try
32                 {
33                     string sql_conn = "Data Source=PQC-PC\\SQLEXPRESS;Initial Catalog=" + textBox1.Text.Trim() + ";Integrated Security=True;Connect Timeout=15;Encrypt=False;
TrustServerCertificate=False";
34                     sqlconn = new SqlConnection(sql_conn);  //建立連接
35                     sqlconn.Open();  //       打開連接
36                     if (sqlconn.State == ConnectionState.Open)   //判斷連接的狀態
37                     { 
38                         listBox1.Items.Add("數據庫連接成功\n");
39                         this.toolStripStatusLabel1.Text = "數據庫連接成功";
40                     } 
41                 }
42                 catch (Exception ex)   //顯示錯誤信息提示
43                 { 
44                     listBox1.Items.Add(ex.Message );
45                     this.toolStripStatusLabel1.Text = "數據庫連接失敗！";
46                 } 
47             }
48             
49         }
50 
51         private void Form2_Load(object sender, EventArgs e)
52         {
53             AcceptButton = button1;
54             listBox1.HorizontalScrollbar = true; listBox1.ScrollAlwaysVisible = true;
55             listBox1.SelectionMode = SelectionMode.MultiExtended;
56 
57         }
58 
59         private void button2_Click(object sender, EventArgs e)
60         {
61             sqlconn.Close();  //Close方法關閉
62             try
63             {
64                 sqlconn.Open();
65                 if (sqlconn.State == ConnectionState.Open)
66                 {
67                     listBox1.Items.Add("恭喜再次連接成功！\n"); this.toolStripStatusLabel1.Text = "ok";
68                 }
69             }
70             catch (Exception ex)
71             { listBox1.Items.Add(ex.Message +"\n"); }
72 
73         }
74 
75         private void button3_Click(object sender, EventArgs e)
76         {
77             sqlconn.Dispose();
78             try
79             {
80                 sqlconn.Open();
81                 if (sqlconn.State == ConnectionState.Open)
82                 {
83                     listBox1.Items.Add("恭喜再次連接成功！\n"); this.toolStripStatusLabel1.Text = "ok";
84                 }
85             }
86             catch (Exception ex)
87             { listBox1.Items.Add(ex.Message + "\n"); toolStripStatusLabel1.Text = "有問題！"; }
88         }
89     }
90 }

?

?

?5.1.使用command對象編輯（增/刪/改）數據表：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Threading.Tasks;
  9 using System.Windows.Forms;
 10 using System.Data.SqlClient;
 11 
 12 
 13 namespace WindowsFormsApplication1
 14 {
 15     public partial class Form3 : Form
 16     {
 17         public Form3()
 18         {
 19             InitializeComponent();
 20         }
 21 
 22         SqlConnection sqlconn;  //連接對象
 23         SqlCommand sqlcomm;     //sql修改對象
 24         SqlDataAdapter sda;      //適配器
 25         
 26         string str_conn = "Data Source=PQC-PC\\SQLEXPRESS;Initial Catalog=SQL;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
 27 
 28 
 29         private void Form3_Load(object sender, EventArgs e)
 30         {
 31             sqlconn = new SqlConnection(str_conn );
 32             sqlcomm = new SqlCommand("select* from[SQL_T]",sqlconn );   //查詢操作
 33             sda = new SqlDataAdapter(sqlcomm );           //實例化適配
 34             DataSet ds1 = new DataSet();                  
 35             sda.Fill(ds1,"cs1" );      //填充ds1數據池
 36             dataGridView1.DataSource=ds1.Tables["cs1"];   //將數據池里的【cs1】表顯示在datagridview控件上
 37 
 38             
 39 
 40         }
 41 
 42         //
 43         private void xianshi_datagridview2()   //創建一個顯示修改后的方法
 44         {
 45             
 46                 //下面將修改后的數據顯示在另一個控件上：
 47             sqlconn = new SqlConnection(str_conn );
 48             sqlcomm = new SqlCommand("select* from[SQL_T]", sqlconn);
 49             sda = new SqlDataAdapter(sqlcomm );
 50             DataSet ds2 = new DataSet();
 51             sda.Fill(ds2, "cs2");
 52             dataGridView2.DataSource = ds2.Tables["cs2"];
 53            
 54         }
 55 
 56         private void zengjia_button_Click(object sender, EventArgs e)
 57         {
 58             if (xingming.Text == "" && banji.Text == "" && xuehao.Text == "" && fenshu.Text == "")  //判斷添加的信息是否齊全
 59             { MessageBox.Show("只有齊全的數據才能添加哦！", "", MessageBoxButtons.OK, MessageBoxIcon.Error); }
 60             else
 61             {
 62                 sqlconn.Open(); //打開連接
 63 
 64                 sqlcomm.CommandText = "INSERT INTO SQL_T ([姓名] ,[班級] ,[學號] ,[分數] ) VALUES("
 65                     + "'" + xingming.Text.Trim() + "'" + "," + "'" + banji.Text.Trim() + "'" + "," + xuehao.Text.Trim() + "," + fenshu.Text.Trim() + ")";
 66                 
 67                 //說明CommandType的屬性為SQL文本命令
 68                 sqlcomm.CommandType = CommandType.Text;
 69                
 70                 //使用ExecuteNonQuery()前要打開連接
 71                 int i = Convert.ToInt32(sqlcomm.ExecuteNonQuery());  
 72 
 73                 this.toolStripStatusLabel1.Text = "已經成功添加了"+i.ToString()+"行數據。";
 74 
 75                 sqlconn.Close();  //關閉數據連接
 76 
 77                 xianshi_datagridview2(); //調用顯示修改后的方法
 78 
 79             
 80             }
 81         }
 82 
 83         private void zengjia_button_MouseCaptureChanged(object sender, EventArgs e)
 84         {
 85           
 86         }
 87 
 88         private void xiugai_button_Click(object sender, EventArgs e)
 89         {
 90             sqlconn.Open();  //打開連接
 91             sqlcomm.CommandText = "update SQL_T set [姓名]=" + "'" + xingming.Text.Trim() + "'" + "," + "[班級]=" + "'" + banji.Text.Trim() + "'" + "," + "[學號]=" + xuehao.Text.Trim()+","+"[分數]="+fenshu.Text.Trim()+"  where [ID]="+id.Text.Trim() ;
 92             sqlcomm.CommandType=CommandType.Text;
 93             int i = Convert.ToInt32(sqlcomm.ExecuteNonQuery());
 94 
 95             this.toolStripStatusLabel1.Text = "修改了" + i.ToString() + "記錄";
 96 
 97             sqlconn.Close();  //關閉數據連接
 98 
 99             xianshi_datagridview2(); //調用顯示修改后的方法
100 
101         }
102 
103         private void shanchu_button_Click(object sender, EventArgs e)
104         {
105             sqlconn.Open();//打開連接
106             sqlcomm.CommandText = "DELETE FROM SQL_T WHERE [ID]=" + id.Text.Trim();
107             sqlcomm.CommandType = CommandType.Text;
108             int i = Convert.ToInt32(sqlcomm.ExecuteNonQuery());
109             this.toolStripStatusLabel1.Text = "已經成功刪除了"+i.ToString()+"句話！";
110             sqlconn.Close();
111             xianshi_datagridview2();
112         }
113     }
114 }

sql是標準化的，?

?檢驗1：

2.增加一條數據（姓名=你好，學號=110，班級=沒有，分數=3）：

SQL Server Management Studio？3.將剛剛增加的數據修改成（姓名=好的，學號=520，班級=不告訴你，分數=100）：

4.刪除這條數據：

?5.2.在（視圖）datagridview控件上修改一條記錄：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 using System.Windows.Forms;
10 using System.Data.SqlClient;
11 
12 
13 namespace WindowsFormsApplication1
14 {
15     public partial class Form4 : Form
16     {
17         public Form4()
18         {
19             InitializeComponent();
20         }
21 
22         SqlConnection sqlconn = new SqlConnection("Data Source=PQC-PC\\SQLEXPRESS;Initial Catalog=SQL;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
23 
24         private void Form4_Load(object sender, EventArgs e)
25         {
26             SqlCommand sqlcomm = new SqlCommand("select* from[SQL_T]",sqlconn );
27             SqlDataAdapter sda = new SqlDataAdapter(sqlcomm );
28             DataSet ds = new DataSet();
29             sda.Fill(ds,"cs" );
30             dataGridView1.DataSource=ds.Tables["cs"];
31 
32 
33         }
34 
35         private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
36         {
37             try
38             {
39                 //將控件中對應的信息賦值給對用的textbox
40                 id.Text = dataGridView1.SelectedCells[4].Value.ToString();
41                 xingming.Text = dataGridView1.SelectedCells[0].Value.ToString();
42                 banji.Text = dataGridView1.SelectedCells[1].Value.ToString();
43                 xuehao.Text = dataGridView1.SelectedCells[2].Value.ToString();
44                 fenshu.Text = dataGridView1.SelectedCells[3].Value.ToString();
45             }
46             catch (Exception ex)
47             { this.toolStripStatusLabel1.Text = ex.Message; }
48         }
49 
50         private void 確定修改_Click(object sender, EventArgs e)
51         {
52             SqlCommand sqlcomm = new SqlCommand("select* from[SQL_T]", sqlconn);
53             SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
54             DataSet ds1 = new DataSet();
55             sda.Fill(ds1,"cs1");
56             DataTable dt1 = new DataTable();
57             dt1=ds1.Tables["cs1"];
58             sda.FillSchema(dt1,SchemaType.Mapped );  
59             DataRow  dr = dt1.Rows.Find (id.Text );  //讀取主鍵行
60             //將textbox中對應的信息賦值給datatable中的對應行信息
61             dr["姓名"] = xingming.Text.Trim();
62             dr["班級"] = banji.Text.Trim();
63             dr["學號"] = xuehao.Text.Trim();
64             dr["分數"] = fenshu.Text.Trim();
65             SqlCommandBuilder sqlcb = new SqlCommandBuilder(sda );
66             sda.Update(dt1 );
67             dataGridView1.DataSource=ds1.Tables["cs1"];
68             toolStripStatusLabel1.Text = "修改成功！";
69 
70         }
71     }
72 }

ADO.NET。?

?檢驗：1.

2.

adonet訪問數據庫？3.

?

5.3.在（視圖）datagridview控件上修改單元格：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 using System.Windows.Forms;
10 using System.Data.SqlClient;
11 
12 
13 namespace WindowsFormsApplication1
14 {
15     public partial class Form5 : Form
16     {
17         public Form5()
18         {
19             InitializeComponent();
20         }
21 
22         SqlConnection sqlconn = new SqlConnection("Data Source=PQC-PC\\SQLEXPRESS;Initial Catalog=SQL;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
23 
24         private void Form5_Load(object sender, EventArgs e)
25         {
26             SqlDataAdapter sda = new SqlDataAdapter("select* from[SQL_T]",sqlconn );
27             DataSet ds = new DataSet();
28             sda.Fill(ds,"cs");
29             dataGridView1.DataSource=ds.Tables["cs"];
30 
31 
32         }
33         private DataTable copy_t()  //復制一個數據表方法
34         { 
35             SqlDataAdapter sda = new SqlDataAdapter("select* from[SQL_T]", sqlconn);
36             DataTable dt = new DataTable();
37             int i = sda.Fill(dt );
38             return dt;
39         }
40         private Boolean up_show()  //更改的方法,其中更改方法中自動調用 copy_t()  //復制一個數據表方法
41         {
42             SqlDataAdapter sda = new SqlDataAdapter("select* from[SQL_T]", sqlconn);
43             DataTable up_dt = copy_t();
44             up_dt.Rows.Clear();
45             DataTable show_dt = (DataTable)this.dataGridView1.DataSource;
46             for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
47             { up_dt.ImportRow(show_dt.Rows[i]); }
48 
49             try
50             {
51                 sqlconn.Open();
52 
53                 sda = new SqlDataAdapter("select* from[SQL_T]", sqlconn );
54                 SqlCommandBuilder sqlcb = new SqlCommandBuilder(sda );
55                 sda.Update(up_dt);
56             }
57             catch (Exception ex)
58             { this.toolStripStatusLabel1.Text = ex.Message; sqlconn.Close(); return false; }
59             up_dt.AcceptChanges();   //提交更改的數據
60             sqlconn.Close();
61             this.toolStripStatusLabel1.Text = "ok";
62             return true;
63         }
64 
65         private void button1_Click(object sender, EventArgs e)
66         {
67             if (up_show())   //調用更改方法
68             { MessageBox.Show("恭喜，數據已經修改成功！"); }
69             else
70             { MessageBox.Show("操作失敗！"); }
71         }
72     }
73 }

?

檢驗：

1. 將姓名為：李異峰，班級=C#.NET；姓名為：李曉峰，學號=880，班級=asp.net;

--在Microsoft SQL Server Management Studio中查詢：

轉載于:https://www.cnblogs.com/liyifeng/p/CS.html

版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。

原文链接：https://hbdhgg.com/2/192923.html

上一篇：2k21電腦無腦包夾，hdu1421 搬寢室 DP
下一篇：Android 開機自啟動
标签：sql server 2008Sql serversql不支持用該后端版本設計數據庫sql是標準化的SQL Server Management StudioADONETadonet訪問數據庫access sql server
sql server 2008

发表评论:

最新文章
阅读排行
猜你喜欢
热门标签

本站为非赢利网站，部分文章来源或改编自互联网及其他公众平台，主要目的在于分享信息，版权归原作者所有，内容仅供读者参考，如有侵权请联系我们删除！

Copyright © 2022 匯編語言學習筆記 Inc. 保留所有权利。

底部版权信息