C# 學習筆記（19）操作SQL Server下
ADO.net操作資料庫
這應該是比較老的技術了，以后有空的話學學 Linq
/// <summary>
/// 資料庫使用類
/// </summary>
public static class MySqlHelper
{
/// <summary>
/// 1.資料庫連接字串
/// </summary>
public static string conStr;
/// <summary>
/// SQL 增 刪 改
/// </summary>
/// <param name="sql">sql命令</param>
/// <param name="ps">sql引數</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
{
try
{
//2.創建連接物件 using結束后會自動釋放資源
using (SqlConnection conn = new SqlConnection(conStr))
{
//3.創建執行SQL陳述句的物件
SqlCommand cmd = new SqlCommand(sql, conn);
//4.撰寫SQL陳述句
cmd.Parameters.AddRange(ps);
cmd.CommandTimeout = 1;
//5.打開SQL連接
conn.Open();
//6.執行SQL陳述句
return cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// SQL 查詢一行資料
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
try
{
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(ps);
cmd.CommandTimeout = 1;
conn.Open();
return cmd.ExecuteScalar();
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// SQL 查詢一塊資料，并把資料存放在一個表中
/// </summary>
/// <param name="sql">命令</param>
/// <param name="ps">引數</param>
/// <returns>記憶體中的表</returns>
public static DataTable ExecuteTable(string sql, params SqlParameter[] ps)
{
try
{
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddRange(ps);
adapter.SelectCommand.CommandTimeout = 1;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// SQL 查詢一塊資料
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] ps)
{
try
{
SqlConnection conn = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(ps);
cmd.CommandTimeout = 1;
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception e)
{
throw e;
}
}
}
修改存盤程序
根據要實作的功能，創建修改對應的存盤程序和視圖，可以一遍寫程式一遍優化存盤程序和視圖
--創建視圖 studentInfoTable
create view studentInfoTable
as
select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別,
gradeTable.name as 班級, (provinceTable.province + cityTable.cityName + isNull(studentTable.address, '')) as 家庭地址
from studentTable
inner join cityTable on cityTable.id = studentTable.cityID
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID
select * from studentInfoTable
--創建視圖 studentMinuteInfoTable
create view studentMinuteInfoTable
as
select studentTable.id as 學生編號, studentTable.name as 姓名, genderTable.name as 性別,
gradeTable.name as 班級, provinceTable.province as 省, cityTable.cityName as 市, studentTable.address as 詳細地址
from studentTable
inner join cityTable on cityTable.id = studentTable.cityID
inner join provinceTable on cityTable.provinceID = provinceTable.id
inner join genderTable on genderTable.id = studentTable.gender
inner join gradeTable on gradeTable.id = studentTable.gradeID
select * from studentMinuteInfoTable
--獲取一頁學生資訊
alter proc GetStudentInfoPage
@pageIndex int, --要查詢的頁序號
@pageSize int --頁大小
as
begin
select * from (select ROW_NUMBER() over(order by 學生編號) as 行序號, * from studentInfoTable) as t1
where 行序號 between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end
exec GetStudentInfoPage 2, 2
--存盤程序 根據省名得到城市表
create proc GetCityInfo
@provinceName nvarchar(10)
as
begin
select cityTable.id, cityName from cityTable
inner join provinceTable on cityTable.provinceID = provinceTable.id
where provinceTable.province = @provinceName
end
exec GetCityInfo '河北'
資料庫隨便填了一些資料，不完整
利用ADO.net操作資料庫
- 創建鏈接字串
//設定資料庫連接字串
//Serever: 表示要連接的SQL Server 服務端所在的機器(可以是ip, 可以是hostname) . 代表本機
//Database: 資料庫名稱
//User Id: 用戶名
//Password: 密碼
//Connection Timeout: 鏈接超時時間
MySqlHelper.conStr = "Data Source = .;Database = TestDB; User Id = sa; Password= 12345678; Connection Timeout = 5;";
- 撰寫sql陳述句
例如要查詢 studentInfoTable 表資料條數
SQL陳述句為
select count(*) from studentInfoTable
c#中將其轉換成字串即可
string sql = "select count(*) from studentInfoTable";
- 添加sql引數（非必要）
如果SQL陳述句需要引數，例如查詢一頁學生資料
SQL陳述句為
exec GetStudentInfoPage 2, 2
c#中
//sql 陳述句
string sql = "exec GetStudentInfoPage @page, @size";
//sql 陳述句引數
SqlParameter page = new SqlParameter("@page", _currentPage);
SqlParameter pageSize = new SqlParameter("@size", PageSize);
- 查詢并顯示資料
//執行不帶參 sql陳述句
int count = (int)MySqlHelper.ExecuteScalar(sql);
//執行帶參 sql陳述句
DataTable dt = MySqlHelper.ExecuteTable(sql, page, pageSize);
- 圖表中顯示查詢到的表
//為GridView的列系結資料源 也就是將dataGridViewSQL的列和 sql查出來表的列名系結起來
dataGridViewSQL.Columns[0].DataPropertyName = "學生編號";
dataGridViewSQL.Columns[1].DataPropertyName = "姓名";
dataGridViewSQL.Columns[2].DataPropertyName = "性別";
dataGridViewSQL.Columns[3].DataPropertyName = "班級";
dataGridViewSQL.Columns[4].DataPropertyName = "家庭地址";
//禁止自動創建列
dataGridViewSQL.AutoGenerateColumns = false;
//系結資料源
dataGridViewSQL.DataSource = dt;
- combox中顯示查詢到的一列資料
//sql 陳述句
string sql = "select * from provinceTable";
//執行sql陳述句
DataTable dt = MySqlHelper.ExecuteTable(sql);
//為combox的列系結資料源 也就是將combox和 sql查出來表的列名系結起來
cmbProvince.DisplayMember = "province";
//系結資料源
cmbProvince.DataSource = dt;
原始碼地址 https://download.csdn.net/download/weixin_42378319/20681950
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CSharp_SQL
{
public partial class Form1 : Form
{
int PageMax = 1;
int PageSize = 3;
int _currentPage = 1;
int CurrentPage
{
get { return _currentPage; }
set
{
//sql 陳述句 查詢行數
string sql = "select count(*) from studentInfoTable";
//執行sql陳述句
PageMax = (int)MySqlHelper.ExecuteScalar(sql);
PageMax = (PageMax + PageSize - 1) / PageSize;
if (value < 1)
{
_currentPage = PageMax;
}
else if(value > PageMax)
{
_currentPage = 1;
}
else
{
_currentPage = value;
}
txbPage.Text = _currentPage.ToString()+"/"+PageMax.ToString();
//sql 陳述句
sql = "exec GetStudentInfoPage @page, @size";
//sql 陳述句引數
SqlParameter page = new SqlParameter("@page", _currentPage);
SqlParameter pageSize = new SqlParameter("@size", PageSize);
//執行sql陳述句
DataTable dt = MySqlHelper.ExecuteTable(sql, page, pageSize);
//為GridView的列系結資料源 也就是將dataGridViewSQL的列和 sql查出來表的列名系結起來
dataGridViewSQL.Columns[0].DataPropertyName = "學生編號";
dataGridViewSQL.Columns[1].DataPropertyName = "姓名";
dataGridViewSQL.Columns[2].DataPropertyName = "性別";
dataGridViewSQL.Columns[3].DataPropertyName = "班級";
dataGridViewSQL.Columns[4].DataPropertyName = "家庭地址";
//禁止自動創建列
dataGridViewSQL.AutoGenerateColumns = false;
//系結資料源
dataGridViewSQL.DataSource = dt;
}
}
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//開啟表格的雙緩沖，避免閃爍
MyDataGridViewDoubleBuffer.SetDoubleBuffered(dataGridViewSQL, true);
//設定資料庫連接字串
//Serever: 表示要連接的SQL Server 服務端所在的機器(可以是ip, 可以是hostname) . 代表本機
//Database: 資料庫名稱
//User Id: 用戶名
//Password: 密碼
//Connection Timeout: 鏈接超時時間
MySqlHelper.conStr = "Data Source = .;Database = TestDB; User Id = sa; Password= 12345678; Connection Timeout = 5;";
CurrentPage = 1;
}
private void labFrontPage_Click(object sender, EventArgs e)
{
CurrentPage -= 1;
}
private void labBackPage_Click(object sender, EventArgs e)
{
CurrentPage += 1;
}
private void txbPage_TextChanged(object sender, EventArgs e)
{
try
{
CurrentPage = int.Parse(txbPage.Text);
}
catch
{
}
}
private void txbPage_MouseDown(object sender, MouseEventArgs e)
{
txbPage.SelectAll();
}
private void 增ToolStripMenuItem_Click(object sender, EventArgs e)
{
FormSQL formSQL = new FormSQL((int)dataGridViewSQL.SelectedRows[0].Cells[0].Value, (string)dataGridViewSQL.SelectedRows[0].Cells[1].Value, (string)dataGridViewSQL.SelectedRows[0].Cells[2].Value, (string)dataGridViewSQL.SelectedRows[0].Cells[3].Value);
formSQL.Text = "增/改資料";
formSQL.ShowDialog();
//重繪
CurrentPage = CurrentPage;
}
private void 刪ToolStripMenuItem_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow item in dataGridViewSQL.SelectedRows)
{
//sql 陳述句
string sql = "delete from studentTable where studentTable.id = @id";
//sql 陳述句引數
SqlParameter id = new SqlParameter("@id", item.Cells[0].Value);
//執行sql陳述句
DataTable dt = MySqlHelper.ExecuteTable(sql, id);
//重繪
CurrentPage = CurrentPage;
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CSharp_SQL
{
public partial class FormSQL : Form
{
int StudentID = 0;
DataTable gradeDT, cityDT;
public FormSQL(int studentID, string name, string gender, string grade)
{
InitializeComponent();
#region 查詢資料庫 獲取省份和班級資訊
//sql 陳述句
string sql = "select * from provinceTable";
//執行sql陳述句
DataTable dt = MySqlHelper.ExecuteTable(sql);
//為combox的列系結資料源 也就是將combox和 sql查出來表的列名系結起來
cmbProvince.DisplayMember = "province";
//系結資料源
cmbProvince.DataSource = dt;
sql = "select * from gradeTable";
//執行sql陳述句
gradeDT = MySqlHelper.ExecuteTable(sql);
//為combox的列系結資料源 也就是將combox和 sql查出來表的列名系結起來
cmbStudentGrade.DisplayMember = "name";
//系結資料源
cmbStudentGrade.DataSource = gradeDT;
#endregion
txbStudentName.Text = name;
if(gender == "男")
{
radMan.Checked = true;
}
else
{
radWoman.Checked = true;
}
cmbStudentGrade.Text = grade;
//獲取學生地址資訊
sql = "select * from studentMinuteInfoTable where 學生編號 = @id";
//sql 陳述句引數
SqlParameter id = new SqlParameter("@id", studentID);
//執行sql陳述句
dt = MySqlHelper.ExecuteTable(sql, id);
cmbProvince.Text = (string)dt.Rows[0].ItemArray[4];
cmbCity.Text = (string)dt.Rows[0].ItemArray[5];
if(dt.Rows[0].ItemArray[6] != System.DBNull.Value)
txbStudentAddr.Text = (string)dt.Rows[0].ItemArray[6];
StudentID = studentID;
}
/// <summary>
/// 省變化時，市資訊跟著變化
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmbProvince_SelectedIndexChanged(object sender, EventArgs e)
{
//sql 陳述句
string sql = "exec GetCityInfo @provinceName";
//sql 陳述句引數
SqlParameter provinceName = new SqlParameter("@provinceName", cmbProvince.Text);
//執行sql陳述句
cityDT = MySqlHelper.ExecuteTable(sql, provinceName);
//為combox的列系結資料源 也就是將combox和 sql查出來表的列名系結起來
cmbCity.DisplayMember = "cityName";
//系結資料源
cmbCity.DataSource = cityDT;
}
private void btnAdd_Click(object sender, EventArgs e)
{
//sql 陳述句
string sql = "insert into studentTable(gradeID, name, gender, cityID, address) Values(@gradeID, @name, @gender, @cityID, @address)";
//sql 陳述句引數
SqlParameter gradeID = new SqlParameter("@gradeID", gradeDT.Rows[cmbStudentGrade.SelectedIndex].ItemArray[0]);
SqlParameter name = new SqlParameter("@name", txbStudentName.Text);
SqlParameter gender;
if(radMan.Checked)
{
gender = new SqlParameter("@gender", true);
}
else
{
gender = new SqlParameter("@gender", false);
}
SqlParameter cityID = new SqlParameter("@cityID", cityDT.Rows[cmbCity.SelectedIndex].ItemArray[0]);
SqlParameter address = new SqlParameter("@address", txbStudentAddr.Text);
MySqlHelper.ExecuteNonQuery(sql, gradeID, name, gender, cityID, address);
this.Close();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
//sql 陳述句
string sql = "update studentTable set gradeID = @gradeID, name = @name, gender = @gender, cityID = @cityID, address = @address where id = @id";
//sql 陳述句引數
SqlParameter id = new SqlParameter("@id", StudentID);
SqlParameter gradeID = new SqlParameter("@gradeID", gradeDT.Rows[cmbStudentGrade.SelectedIndex].ItemArray[0]);
SqlParameter name = new SqlParameter("@name", txbStudentName.Text);
SqlParameter gender;
if (radMan.Checked)
{
gender = new SqlParameter("@gender", true);
}
else
{
gender = new SqlParameter("@gender", false);
}
SqlParameter cityID = new SqlParameter("@cityID", cityDT.Rows[cmbCity.SelectedIndex].ItemArray[0]);
SqlParameter address = new SqlParameter("@address", txbStudentAddr.Text);
MySqlHelper.ExecuteNonQuery(sql, gradeID, name, gender, cityID, address, id);
this.Close();
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/291720.html
標籤：其他