public partial class Form1 : Form
{
Form1數(shù)據(jù)成員#region Form1數(shù)據(jù)成員
private DataTable DT = new DataTable();
private SqlDataAdapter SDA = new SqlDataAdapter();
#endregion
Form1構(gòu)造函數(shù)#region Form1構(gòu)造函數(shù)
public Form1()
{
InitializeComponent();
}
#endregion
連接數(shù)據(jù)庫顯示數(shù)據(jù)#region 連接數(shù)據(jù)庫顯示數(shù)據(jù)
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=127.0.0.1;database=pubs;uid=sa");
SqlCommand SCD = new SqlCommand("select * from tables", conn);
SDA.SelectCommand = SCD;
SDA.Fill(DT);
dataGridView1.DataSource = DT;
}
#endregion
使用Update更新數(shù)據(jù)庫#region 使用Update更新數(shù)據(jù)庫
private void toolStripButton1_Click(object sender, EventArgs e)
{
try
{
SqlCommandBuilder SCB = new SqlCommandBuilder(SDA);
SDA.Update(DT);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("更新成功!");
}
#endregion
1 #region 關(guān)于數(shù)據(jù)庫操作的函數(shù)集，與業(yè)務無關(guān)
2
3 /// <summary>
4 /// 查詢數(shù)據(jù)庫記錄,返回存放記錄的DataTable
5 /// </summary>
6 /// <param name="Sql">SQL查詢語句</param>
7 /// <returns>DataTable數(shù)據(jù)表</returns>
8 public DataTable DB_Find(string Sql)
9 {
10 SqlConnection dbConn = new SqlConnection(Param_Class.Param_DB.strConn);
11 dbConn.Open();
12 SqlDataAdapter Sda = new SqlDataAdapter(Sql, dbConn);
13 DataTable dt = new DataTable();
14 Sda.Fill(dt);
15 dbConn.Close();
16 return dt;
17 }
18
19 /// <summary>
20 /// 查詢數(shù)據(jù)庫記錄，返回存放記錄的DataTable，并指定其名稱
21 /// </summary>
22 /// <param name="Sql">SQL查詢語句</param>
23 /// <param name="TableName">指定DataTable的名稱</param>
24 /// <returns>以TableName命名的數(shù)據(jù)表</returns>
25 public DataTable DB_Find(string Sql, string TableName)
26 {
27 SqlConnection dbConn = new SqlConnection(Param_Class.Param_DB.strConn);
28 dbConn.Open();
29 SqlDataAdapter Sda = new SqlDataAdapter(Sql, dbConn);
30 DataSet ds = new DataSet();
31 Sda.Fill(ds, TableName);
32 dbConn.Close();
33 return ds.Tables[TableName];
34 }
35
36 /// <summary>
37 /// 查找數(shù)據(jù)表中是否存在某個記錄
38 /// </summary>
39 /// <param name="Sql">SQL查詢語句</param>
40 /// <returns>整形變量，0－沒有符合記錄；大于0－找到符合記錄</returns>
41 public int IsRecorderExist(string Sql)
42 {
43 SqlConnection dbConn = new SqlConnection(Param_Class.Param_DB.strConn);
44 dbConn.Open();
45 SqlDataAdapter Sda = new SqlDataAdapter(Sql, dbConn);
46 DataTable dt = new DataTable();
47 Sda.Fill(dt);
48 dbConn.Close();
49 return dt.Rows.Count;
50 }
51
52 /// <summary>
53 /// 在對應的數(shù)據(jù)表里添加新記錄
54 /// </summary>
55 ///<param name="strTableName">需要添加記錄的數(shù)據(jù)表</param>
56 /// <param name="dt">需要添加記錄的數(shù)據(jù)表所暫存的DataTable</param>
57 /// <param name="strValues">新記錄的各字段值組成的字符串數(shù)組</param>
58 public void Db_AddNew(string strTableName, DataTable dt, string[] strValues)
59 {
60 try
61 {
62 string[] strDesField = new string[100];
63 string strSql = "", strField = "", strValue = "";
64 for (int i = 0; i < dt.Columns.Count; i++)
65 {
66 strDesField[i] = dt.Columns[i].ColumnName;
67 strField += strDesField[i] + ",";
68 strValue += "'" + strValues[i] + "',";
69 }
70 int nPos = strField.LastIndexOf(@",");
71 strField = strField.Substring(0, nPos);
72 nPos = strValue.LastIndexOf(@",");
73 strValue = strValue.Substring(0, nPos);
74 strSql = String.Format("INSERT INTO {0}({1}) VALUES({2})", strTableName, strField, strValue);
75 SqlConnection dbConn = new SqlConnection(Param_Class.Param_DB.strConn);
76 SqlCommand cmdAddNew = new SqlCommand(strSql, dbConn);
77 dbConn.Open();
78 SqlDataReader Sdr = cmdAddNew.ExecuteReader();
79 Sdr.Close();
80 dbConn.Close();
81 }
82 catch (Exception ex)
83 {
84 MessageBox.Show("操作失敗，原因：" + ex.ToString());
85 }
86 }
87
88 /// <summary>
89 /// 在對應的數(shù)據(jù)表里刪除記錄
90 /// </summary>
91 /// <param name="strTableName">源數(shù)據(jù)表名</param>
92 /// <param name="strKey">數(shù)據(jù)表主鍵</param>
93 /// <param name="strFilter">主鍵的匹配值</param>
94 public void DB_Delete(string strTableName, string strKey, string strFilter)
95 {
96 try
97 {
98 string strSql = String.Format("DELETE FROM {0} WHERE {1}='{2}'", strTableName, strKey, strFilter);
99 SqlConnection dbConn = new SqlConnection(Param_Class.Param_DB.strConn);
100 SqlCommand cmdDel = new SqlCommand(strSql, dbConn);
101 dbConn.Open();
102 SqlDataReader Sdr = cmdDel.ExecuteReader();
103 Sdr.Close();
104 dbConn.Close();
105 }
106 catch (Exception ex)
107 {
108 MessageBox.Show("操作失敗，原因：" + ex.ToString());
109 }
110 }
111
112 /// <summary>
113 /// 更新數(shù)據(jù)庫中與參數(shù)中的SQL查詢符合的記錄,針對單條記錄修改
114 /// </summary>
115 /// <param name="strSql">查詢某條需要修改的記錄的SQL語句</param>
116 /// <param name="strValue">各字段的新值，字符串數(shù)組</param>
117 /// <returns>更新后的數(shù)據(jù)表DataTable</returns>
118 public DataTable DB_Update(string strSql, string[] strValue)
119 {
120 DataTable dt = new DataTable();
121 dt = DB_Find(Param_Class.Param_DB.strConn, strSql);
122 DataTable dtNew = new DataTable();
123 for (int i = 0; i < dt.Columns.Count; i++)
124 {
125 dt.Rows[0][dt.Columns[i].ColumnName] = strValue[i];
126 }
127 SqlDataAdapter Sda = new SqlDataAdapter(strSql, Param_Class.Param_DB.strConn);
128 SqlCommandBuilder cmbUpdate = new SqlCommandBuilder(Sda);
129 Sda.Update(dt);
130 dt.AcceptChanges();
131 return dt;
132 }
133 #endregion
本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
