C#数据库操作类完整通用.docx

1、C#数据库操作类完整通用using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Configuration;using System.Data;namespace MSSQL数据库操作类 public class SqlServer SqlConnection _con = new SqlConnection(); SqlCommand _cmd = new SqlCommand(); SqlDataAdapter _sda = new Sq

2、lDataAdapter(); public SqlServer() : this(ConfigurationManager.ConnectionStringsMSSQLConStr.ConnectionString) public SqlServer(string connectionString) this._con.ConnectionString = connectionString; this._cmd.Connection = this._con; public void Reset() this._cmd.Connection = this._con; this._cmd.Com

3、mandType = CommandType.Text; this.SQL = string.Empty; this.Parameters.Clear(); public void Open() if (this._con.State = ConnectionState.Closed) this._con.Open(); public void Close() if (this._con.State = ConnectionState.Open) this._con.Close(); public CommandType CommandType get return this._cmd.Com

4、mandType; set this._cmd.CommandType = value; public string SQL get return this._cmd.CommandText; set this._cmd.CommandText = value; public SqlParameterCollection Parameters get return this._cmd.Parameters; public string GetDeleteSQL(string tableName, string condition) if (!string.IsNullOrEmpty(condi

5、tion) condition = WHERE + condition; return string.Format(DELETE FROM 0 1, tableName, condition); /SELECT TOP 个数 fields FROM tableName WHERE 条件 public string GetSelectSQL(string table, string condition, int top, string fields, string orderBy) #region 不太好的版本 /string sql = SELECT ; /if (top 1) / / sql

6、 += TOP + top.ToString()+ ; / /if (!string.IsNullOrEmpty(fields) / / sql += fields; / /else / / sql += * FROM ; / /sql += tableName; /if (!string.IsNullOrEmpty(condition) / / condition = WHERE + condition; / /sql += condition; /if (!string.IsNullOrEmpty(orderBy) / / orderBy = ORDER BY + orderBy; / /

7、sql += orderBy; #endregion string t = top 0 ? TOP + top.ToString() : ; if (string.IsNullOrEmpty(fields) fields = *; if (!string.IsNullOrEmpty(condition) condition = WHERE + condition; if (!string.IsNullOrEmpty(orderBy) orderBy = ORDER BY + orderBy; string sql = string.Format(SELECT 0 1 FROM 2 3 4, t

8、, fields, table, condition, orderBy); return sql; public string GetSelectSQL(string table, string condition, string fields, string orderBy) return this.GetSelectSQL(table, condition, 0, fields, orderBy); public string GetGagingSelectSQL(string table, string condition, string fields, string orderBy)

9、string sql = SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY 0) AS RowNum,1 FROM 2 3 ) AS T WHERE RowNum BETWEEN (CurrentPage-1)*PageSize+1 AND CurrentPage*PageSize ORDER BY 0; if (!string.IsNullOrEmpty(condition) condition = WHERE + condition; if (string.IsNullOrEmpty(fields) fields = *; return

10、string.Format(sql, orderBy, fields, table, condition); public string GetInsertSQL(string table) /INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES(Sno,Sname,Ssex,Sage,Sdept) string sql = INSERT INTO 0(1)VALUES(2); string field = string.Empty; string parameter = string.Empty; foreach (SqlParameter

11、 spr in this.Parameters) field += spr.ParameterName.Remove(0, 1) + ,; parameter += spr.ParameterName + ,; field = field.Remove(field.Length - 1, 1); parameter = parameter.Remove(parameter.Length - 1, 1); return string.Format(sql, table, field, parameter); public string GetUpdateSQL(string table, str

12、ing condition) /UPDATE Student SET Sno=Sno,Sname=Sname WHERE Sno=Sno AND . string sql = UPDATE 0 SET 1 2; string str = string.Empty; foreach (SqlParameter spr in this.Parameters) str += spr.ParameterName.Remove(0, 1) + = + spr.ParameterName + ,; str = str.Remove(str.Length - 1, 1); if (!string.IsNul

13、lOrEmpty(condition) condition = WHERE + condition; return string.Format(sql, table, str, condition); / / 添加SqlParameter，需要5个属性值 / / 参数名 / 参数数据类型 / 大小 / 源列名 / 值 / 被添加进去的参数对象 public SqlParameter AddSqlParameter(string parameterName, SqlDbType sqlDbType, int size, string sourceColumn, object value) Sql

14、Parameter spr = new SqlParameter(); spr.ParameterName = parameterName; spr.SqlDbType = sqlDbType; if (size 0) spr.Size = size; if (!string.IsNullOrEmpty(sourceColumn) spr.SourceColumn = sourceColumn; spr.Value = (value = null) ? DBNull.Value : value; return this._cmd.Parameters.Add(spr); / / 添加SqlPa

15、rameter，需要4个属性值 / / 参数名 / 参数数据类型 / 源列名 / 值 / 被添加进去的参数对象 public SqlParameter AddSqlParameter(string parameterName, SqlDbType sqlDbType, string sourceColumn, object value) return this.AddSqlParameter(parameterName, sqlDbType, 0, sourceColumn, value); / / 添加SqlParameter，需要3个属性值 / / 参数名 / 参数数据类型 / 值 / 被

16、添加进去的参数对象 public SqlParameter AddSqlParameter(string parameterName, SqlDbType sqlDbType, object value) return this.AddSqlParameter(parameterName, sqlDbType, null, value); / / 添加SqlParameter，需要2个属性值 / / 参数名 / 参数数据类型 / 值 / 被添加进去的参数对象 public SqlParameter AddSqlParameter(string parameterName, object val

17、ue) SqlParameter spr = new SqlParameter(); spr.ParameterName = parameterName; spr.Value = (value = null) ? DBNull.Value : value; return this._cmd.Parameters.Add(spr); public void ClearSqlParameter() this._cmd.Parameters.Clear(); #region 执行UPDATE、INSERT、DELETE，返回受影响的行数 public int ExcuteNonQuery() try

18、 this.Open(); return _cmd.ExecuteNonQuery(); catch (Exception ex) throw ex; finally this.Close(); #endregion #region 执行SELECT COUNT(*) FROM.类似的SQL语句，返回首行首列 public object ExcuteScalar() try this.Open(); return _cmd.ExecuteScalar(); catch (Exception ex) throw ex; finally this.Close(); #endregion #regi

19、on 执行SELECT 属性列表 FROM.类似的SQL语句，返回首行首列 public SqlDataReader ExcuteReader() try this.Open(); return _cmd.ExecuteReader(); catch (Exception ex) throw ex; finally / if (con.State = ConnectionState.Open) con.Close(); /可以使用委托机制来解决数据无法关闭的问题 #endregion #region 执行SELECT 属性列表 FROM.类似的SQL语句，返回一个内存中的数据库 public

20、DataSet GetDataSet() try this._sda.SelectCommand = this._cmd; DataSet ds = new DataSet(); this._sda.Fill(ds); return ds; catch (Exception ex) throw ex; finally #endregion #region 有缺陷的写法，返回的数据表不能和其它的数据库任意进行合并等操作，因为其和ds有关系 /public DataTable GetDataTable(string SQL) / / DataSet ds = this.GetDataSet(SQL); / return ds.Tables0; / #endregion #region 执行SELECT 属性列表 FROM.类似的SQL语句，返回一个内存中的数据表 public DataTable GetDataTable() try this._sda.SelectCommand = this._cmd; DataTable dt = new DataTable(); this._sda.Fill(dt); return dt; catch (Exception ex) throw ex; finally #endregion