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 ///
4 /// 查询数据库记录,返回存放记录的DataTable
5 ///
6 /// SQL查询语句
7 /// DataTable数据表
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 ///
20 /// 查询数据库记录，返回存放记录的DataTable，并指定其名称
21 ///
22 /// SQL查询语句
23 /// 指定DataTable的名称
24 /// 以TableName命名的数据表
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 ///
37 /// 查找数据表中是否存在某个记录
38 ///
39 /// SQL查询语句
40 /// 整形变量，0－没有符合记录；大于0－找到符合记录
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 ///
53 /// 在对应的数据表里添加新记录
54 ///
55 ///需要添加记录的数据表
56 /// 需要添加记录的数据表所暂存的DataTable
57 /// 新记录的各字段值组成的字符串数组
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 ///
89 /// 在对应的数据表里删除记录
90 ///
91 /// 源数据表名
92 /// 数据表主键
93 /// 主键的匹配值
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 ///
113 /// 更新数据库中与参数中的SQL查询符合的记录,针对单条记录修改
114 ///
115 /// 查询某条需要修改的记录的SQL语句
116 /// 各字段的新值，字符串数组
117 /// 更新后的数据表DataTable
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
阅读(10516) | 评论(0) | 转发(0) |
