|
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è)務(wù)無關(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 /// 在對應(yīng)的數(shù)據(jù)表里添加新記錄 54 /// </summary> 55 ///<param name="strTableName">需要添加記錄的數(shù)據(jù)表</param> 56 /// <param name="dt">需要添加記錄的數(shù)據(jù)表所暫存的DataTable</param> 57 /// <param name="strValues">新記錄的各字段值組成的字符串?dāng)?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 /// 在對應(yīng)的數(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">各字段的新值，字符串?dāng)?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 |
|
|
