学步园

一:概述(所有遇到的数据库的相关操作都是用存储过程)

(1)采用处理流程::::基类DBClass,根据传递的存储过程名称,和参数调用存储过程,,, Fac***类根据页面需要进行逻辑处理,并在数据库中加入所需存储过程,然后调用DBClass,执行存错过程,,,,,,各个页面对应CS中调用Fac**类进行数据的操作......页面不能跨过Fac直接调用DBClass

　　　　实施过程:DBClass写好后不在变动,前台人员--创建页面,调用Fac**方法;后台人员--根据业务需求,编写存储过程,并为前台编写Fac**方法

(2)与数据库的连接字符串(包含数据库地址,数据库名称,登陆用户名及密码信息) ..放到web.config配置文件中..系统启动时根据权限需求,调用不同的链接字符串..这样可以在数据库中为不同登陆用户设置不同权限,也方便统一管理连接字符串.

　　　　实现方法:在web.config中添加类似<appSettings><add key="SqlConnection" value="server=192.168.1.27;database=InfoOnline;uid=admin;pwd=111111;" /></appSettings>的键值对,程序中根据key值取得value值.

　　　　　　　　 程序中:先导入名称空间 ,System.Configuration;在用ConfigurationSettings.AppSettings[key]; 

 using System.Configuration;
public SqlConnection GetConnection(string key)
        {
            //Zeda .Common .Encrypt .EncryptString enstr=new EncryptString ();
            if (ConfigurationSettings.AppSettings[key] != null)
            {
                string text1 = ConfigurationSettings.AppSettings[key];
                this._connection = new SqlConnection(text1.Trim());
                return this._connection;
            }
            throw new Exception("config文件不存在");
        } 

(3)与数据库的操作:数据库与程序本是独立的,,插入一层作为桥梁,,那么就通了,各语言与数据库的操作基本相同,主要就是建立连接,打开连接,进行操作,关闭连接..

其中不一样的就是操作部分,如执行sql查询语句

        public DataTable TestRunProcedure1()
        {
            DataSet dset = new DataSet();
            //建立连接
            SqlConnection sqlCon = new SqlConnection("server=192.168.1.27;database=InfoOnline;uid=qqq;pwd=111111;");
            //进行操作
            if(sqlCon != null)
            {
                //创建Command
                SqlCommand command =  new SqlCommand("select * from aTest", sqlCon);
                sqlCon.Open();
                //  int rowAffact = command.ExecuteNonQuery();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dset);
            }
            //关闭连接
            sqlCon.Close();
            return dset.Tables[0];
        }

执行sql更新(删除,添加)语句

public int TestRunProcedure2()
        {
            int rowAffact = 0;
            //建立连接
            SqlConnection sqlCon = new SqlConnection("server=192.168.1.27;database=InfoOnline;uid=qqq;pwd=111111;");
            //进行操作
            if(sqlCon != null)
            {
                //创建Command
                SqlCommand command =  new SqlCommand("delete from aTest where id > 25", sqlCon);
                sqlCon.Open();
                rowAffact = command.ExecuteNonQuery();
            }
            //关闭连接
            sqlCon.Close();
            return rowAffact;
        }

当执行的是存储过程时,指定command.CommandType=CommandType.StoredProcedure;,并为其添加参数即可command.Parameters.Add(parameter1);其他一样,如

public int TestRunProcedure3()
        {
            int rowAffact = 0;
            //建立连接
            SqlConnection sqlCon = new SqlConnection("server=192.168.1.27;database=InfoOnline;uid=qqq;pwd=111111;");
            //进行操作
            if(sqlCon != null)
            {
                //创建Command
                sqlCon.Open();
                SqlParameter[] paras = 
                {
                    new SqlParameter("@NewsID",1565),
                    new SqlParameter("@Hits",1)
                };
                SqlCommand command =  new SqlCommand("[LC_News_tNews_Update_Hits]", sqlCon);
                foreach (SqlParameter parameter1 in paras)
                {
                    command.Parameters.Add(parameter1);
                }
                command.CommandType=CommandType.StoredProcedure;
                rowAffact = command.ExecuteNonQuery();
            }
            //关闭连接
            sqlCon.Close();
            return rowAffact;
        }

基本步骤:

           //建立连接
            SqlConnection sqlCon = new SqlConnection("server=192.168.1.27;database=InfoOnline;uid=qqq;pwd=111111;");

　　　　//打开连接

　　　　sqlCon.Open();

            //进行操作　　　　

            -------　　          

           //关闭连接          

           sqlCon.Close(); 

 操作部分:主要用SqlCommand,

　　　　首先定义:SqlCommand command =  new SqlCommand("delete from aTest where id > 25", sqlCon);

　　　　若是增删改,

　　　　　　　　则可以直接使用:int rowAffact = command.ExecuteNonQuery();//返回影响的行数

 　　　  若是查询,则可以使用SqlDataAdapter,

　　　　　　　　首先定义:::>>>SqlDataAdapter adapter = new SqlDataAdapter();

　　　　　　　　为adapter指定command::>>>adapter.SelectCommand = command;

　　　　　　　　填充dataset >>> adapter.Fill(dset);//其中dset定义为:DataSet dset = new DataSet();是表集合

　　　　　　　　可获得表DataTable dt = dset.Tables[0];

二:实际使用代码:

DBClass类:

  1 using System;
  2 
  3 namespace WebApplication1.Base
  4 {
  5     using System;
  6      using System.Configuration;
  7     using System.Data;
  8     using System.Data.SqlClient;
  9     using System.Runtime.InteropServices;
 10 
 11 
 12     public class DbClass : IDisposable
 13     {
 14         //
 15         private SqlConnection _connection;
 16         public SqlConnection Connection
 17         {
 18             get
 19             {
 20                 return this._connection;
 21             }
 22         }
 23 
 24         //构造函数
 25         public DbClass()
 26         {
 27             this._connection = this.GetConnection("UserManagerConnection");
 28         }
 29 
 30         public DbClass(string key)
 31         {
 32             this._connection = this.GetConnection(key);
 33         }
 34 
 35         //从Web.config中读取配置参数(数据库连接字符串及用户名密码)
 36         public SqlConnection GetConnection(string key)
 37         {
 38             //Zeda .Common .Encrypt .EncryptString enstr=new EncryptString ();
 39             if (ConfigurationSettings.AppSettings[key] != null)
 40             {
 41                 string text1 = ConfigurationSettings.AppSettings[key];
 42                 this._connection = new SqlConnection(text1.Trim());
 43                 return this._connection;
 44             }
 45             throw new Exception("config文件不存在");
 46         }
 47 
 48         private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
 49         {
 50             SqlCommand command1 = this.BuildQueryCommand(storedProcName, parameters);
 51             command1.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
 52             return command1;
 53         }
 54 
 55         private SqlCommand BuildQueryCommand(string storedProcedureName, IDataParameter[] parameters)
 56         {
 57             return this.BuildQueryCommand(storedProcedureName, parameters, CommandType.StoredProcedure);
 58         }
 59 
 60         private SqlCommand BuildQueryCommand(string storedProcNameOrSqlString, IDataParameter[] parameters, CommandType type)
 61         {
 62             SqlCommand command1 = new SqlCommand(storedProcNameOrSqlString, this._connection);
 63             command1.CommandType = type;
 64             foreach (SqlParameter parameter1 in parameters)
 65             {
 66                 command1.Parameters.Add(parameter1);
 67             }
 68             return command1;
 69         }
 70 
 71         //更改连接
 72         public void ChangeConnection(string connectionString)
 73         {
 74             this._connection = new SqlConnection(connectionString);
 75         }
 76 
 77         //释放资源
 78         public void Dispose()
 79         {
 80             this.Dispose(true);
 81             GC.SuppressFinalize(this);
 82         }
 83 
 84         protected virtual void Dispose(bool disposing)
 85         {
 86             if (disposing && (this._connection != null))
 87             {
 88                 this._connection.Dispose();
 89             }
 90         }
 91 
 92         ~DbClass()
 93         {
 94             this.Dispose(false);
 95         }
 96 
 97         public SqlCommand GetCommandForSqlSentence(string sqlstr, IDataParameter[] parameters)
 98         {
 99             return this.BuildQueryCommand(sqlstr, parameters, CommandType.Text);
100         }
101 
102         
103 
104         public SqlDataReader GetDataReader(string str_sel)
105         {
106             SqlCommand command1 = new SqlCommand(str_sel, this._connection);
107             this._connection.Open();
108             return command1.ExecuteReader(CommandBehavior.CloseConnection);
109         }
110 
111         public DataSet GetDataSet(string str_sel)
112         {
113             SqlDataAdapter adapter1 = new SqlDataAdapter(str_sel, this._connection);
114             DataSet set1 = new DataSet();
115             try
116             {
117                 this._connection.Open();
118                 adapter1.Fill(set1);
119             }
120             finally
121             {
122                 this._connection.Close();
123                 this._connection.Dispose();
124             }
125             return set1;
126         }
127 
128         public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
129         {
130             SqlDataReader reader1;
131             try
132             {
133                 this._connection.Open();
134                 SqlCommand command1 = this.BuildQueryCommand(storedProcName, parameters);
135                 command1.CommandType = CommandType.StoredProcedure;
136                 reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection);
137             }
138             catch (SqlException exception1)
139             {
140                 this._connection.Close();
141                 throw exception1;
142             }
143             return reader1;
144         }
145 
146         /// <summary>
147         /// 执行存储过程(查询)
148         /// </summary>
149         /// <param name="storedProcName">存储过程名称</param>
150         /// <param name="parameters">参数</param>
151         /// <param name="tableName">表名</param>
152         /// <returns>数据集</returns>
153         public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
154         {
155             DataSet set1 = new DataSet();
156             try
157             {
158                 this._connection.Open();
159                 SqlDataAdapter adapter1 = new SqlDataAdapter();
160                 adapter1.SelectCommand = this.BuildQueryCommand(storedProcName, parameters);
161                 adapter1.Fill(set1, tableName);
162             }
163             finally
164             {
165                 this._connection.Close();
166             }
167             return set1;
168         }
169 
170         /// <summary>
171         /// 执行存储过程(增,删,改)
172         /// </summary>
173         /// <param name="storedProcName"></param>
174         /// <param name="parameters"></param>
175         /// <param name="rowsAffected"></param>
176         /// <returns></returns>
177         public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
178         {
179             int num1;
180             try
181             {
182                 this._connection.Open();
183                 SqlCommand command1 = this.BuildIntCommand(storedProcName, parameters);
184                 rowsAffected = command1.ExecuteNonQuery();
185                 num1 = (int) command1.Parameters["ReturnValue"].Value;
186             }
187             finally
188             {
189                 this._connection.Close();
190             }
191             return num1;
192         }
193     }
194 }
195  
196  
197  
198 

Facade类

  1 using System;
  2 using System.Data;
  3 using System.Data.SqlClient;
  4 using WebApplication1.Base;
  5 
  6 namespace WebApplication1.Facade
  7 {
  8     /// <summary>
  9     /// DCtNews 的摘要说明。
 10     /// </summary>
 11     public class DCtNews 
 12     {
 13         int rowsAffected = -100;
 14         DataSet ds;
 15         
 16         public DCtNews()
 17         {
 18             //
 19             // TODO: 在此处添加构造函数逻辑
 20             //
 21         }
 22 
 23         /// <summary>
 24         /// 根据条件分页获取新闻列表
 25         /// </summary>
 26         /// <param name="fields">要查询的字段名称 默认：*</param>
 27         /// <param name="where">查询条件，不带 Where 关键字，例如 moduleCode=3000 and inPass=1









														

返回