public partial class Form1 : Form
{ Form1数据成员#region Form1数据成员 private DataTable DT = new DataTable(); private SqlDataAdapter SDA = new SqlDataAdapter(); #endregion Form1构造函数#region Form1构造函数 public Form1() { InitializeComponent(); } #endregion 连接数据库显示数据#region 连接数据库显示数据 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更新数据库#region 使用Update更新数据库 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 关于数据库操作的函数集,与业务无关
2 3 /// <summary> 4 /// 查询数据库记录,返回存放记录的DataTable 5 /// </summary> 6 /// <param name="Sql">SQL查询语句</param> 7 /// <returns>DataTable数据表</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 /// 查询数据库记录,返回存放记录的DataTable,并指定其名称 21 /// </summary> 22 /// <param name="Sql">SQL查询语句</param> 23 /// <param name="TableName">指定DataTable的名称</param> 24 /// <returns>以TableName命名的数据表</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 /// 查找数据表中是否存在某个记录 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 /// 在对应的数据表里添加新记录 54 /// </summary> 55 ///<param name="strTableName">需要添加记录的数据表</param> 56 /// <param name="dt">需要添加记录的数据表所暂存的DataTable</param> 57 /// <param name="strValues">新记录的各字段值组成的字符串数组</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 /// 在对应的数据表里删除记录 90 /// </summary> 91 /// <param name="strTableName">源数据表名</param> 92 /// <param name="strKey">数据表主键</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 /// 更新数据库中与参数中的SQL查询符合的记录,针对单条记录修改 114 /// </summary> 115 /// <param name="strSql">查询某条需要修改的记录的SQL语句</param> 116 /// <param name="strValue">各字段的新值,字符串数组</param> 117 /// <returns>更新后的数据表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 |
|
来自: Cloud书屋 > 《DataGridView》