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
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