.net之 调用存储过程

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

(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</param>
 28         /// <param name="order">排序字段，例如：editDate desc,newsId asc</param>
 29         /// <param name="beginindex">开始记录（从第几条记录开始）默认：1</param>
 30         /// <param name="pageindex">当前页数（当前页索引）默认：1</param>
 31         /// <param name="pagesize">分页记录数量（一页多少条记录）默认：10</param>
 32         /// <param name="pagecount">out:返回总页数</param>
 33         /// <param name="rowscount">out:返回总记录数</param>
 34         /// <returns>DataTable：返回查询结果</returns>
 35         public DataTable GetList_ByPage(string fields, string where,string order,int beginindex,int pageindex,int pagesize,out int pagecount,out int rowscount)
 36         {
 37             pagecount = 1;
 38             rowscount = 0;
 39             SqlParameter[] paras = {
 40                                        new SqlParameter("@Fields",fields),
 41                                        new SqlParameter("@Where",where),
 42                                        new SqlParameter("@Order",order),
 43                                        new SqlParameter("@BeginIndex",beginindex),
 44                                        new SqlParameter("@PageIndex",pageindex),
 45                                        new SqlParameter("@PageSize",pagesize),
 46                                        new SqlParameter("@PageCount",pagecount),
 47                                        new SqlParameter("@RowsCount",rowscount)};
 48             paras[6].Direction = ParameterDirection.Output;
 49             paras[7].Direction = ParameterDirection.Output;
 50 
 51             DbClass dbclass = new DbClass();
 52             dbclass.GetConnection("CMSConnectionString");
 53             ds = dbclass.RunProcedure("[LC_News_tNews_GetList_Where_ByPage]",paras,"tNews");
 54 
 55             if(ds != null && ds.Tables.Count>0)
 56             {
 57                 pagecount = (int)paras[6].Value;
 58                 rowscount = (int)paras[7].Value;
 59                 return ds.Tables["tNews"];
 60             }
 61             return null;
 62         }
 63         /// <summary>
 64         /// 
 65         /// </summary>
 66         /// <param name="where"></param>
 67         /// <param name="order"></param>
 68         /// <param name="beginindex"></param>
 69         /// <param name="pageindex"></param>
 70         /// <param name="pagesize"></param>
 71         /// <param name="pagecount"></param>
 72         /// <param name="rowscount"></param>
 73         /// <returns></returns>
 74         public DataTable GetList_ByPage(string where,string order,int beginindex,int pageindex,int pagesize,out int pagecount,out int rowscount)
 75         {
 76             return GetList_ByPage("*",where,order,beginindex,pageindex,pagesize,out pagecount,out rowscount);
 77         }
 78 
 79 
 80         /// <summary>
 81         /// 获取新闻内容
 82         /// </summary>
 83         /// <param name="newsid">新闻ID</param>
 84         /// <returns></returns>
 85         public DataTable GetModel_ByID(int newsid)
 86         {
 87             int pages = 1;int rows = 0;
 88             return GetList_ByPage("NewsID=" + newsid.ToString(),"NewsID",1,1,1,out pages,out rows);
 89         }
 90 
 91         /// <summary>
 92         /// 更新新闻点击次数
 93         /// </summary>
 94         /// <param name="newsid">新闻ID</param>
 95         /// <param name="hits">要增加的次数</param>
 96         public void Update_Hits(int newsid,int hits)
 97         {
 98             SqlParameter[] paras = {
 99                                        new SqlParameter("@NewsID",newsid),
100                                        new SqlParameter("@Hits",hits)
101                                    };
102             DbClass dbclass = new DbClass();
103             dbclass.GetConnection("CMSConnectionString");
104             dbclass.RunProcedure("[LC_News_tNews_Update_Hits]",paras,out rowsAffected);
105         }
106 
107     }//class end
108 }//namespace end
109 

 页面调用方法

             // 在此处放置用户代码以初始化页面
2             tnews = new WebApplication1.Facade.DCtNews();
3             int pagecount ,rowscount;
4             dt = tnews.GetList_ByPage("*","moduleCode=100 and isPass=1","inDate desc,newsId desc",1,1,15,out pagecount,out rowscount);
5             this.DataGrid1.DataSource = dt;
6             this.DataGrid1.DataBind();

存储过程代码(举例):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

------------------------------------
--用途：按条件分页查询新闻列表
--说明：
--时间：2009-10-14 9:15:30
------------------------------------
ALTER PROCEDURE [dbo].[Record_tNews_Select_Module]
@Fields varchar(1000),    --查询字段
@Where varchar(1000),    --查询条件
@Order varchar(100),    --排序字段（带条件 desc/asc）
@BeginIndex int,    --开始记录数
@PageIndex int,        --当前页数
@PageSize int,        --页大小
@PageCount int output,    --总页数输出
@RowsCount int output    --总记录数输出
AS

SET NOCOUNT ON
  IF @PageSize < 0 OR @PageIndex < 0
  BEGIN        
  RETURN
  END

DECLARE @Table varchar(100)    --表名
--DECLARE @Fields varchar(500)--字段列表
SET @Table = 'tNews'
--SET @Fields = '*'

DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @Sql VARCHAR(4000)
DECLARE @SqlCount NVARCHAR(4000)

DECLARE @Top int

set @BeginIndex = @BeginIndex - 1

IF ISNULL(@where,'') = ''
    SET @new_where1 = ' '
ELSE
    SET @new_where1 = ' WHERE ' + @where 

IF ISNULL(@order,'') <> '' 
BEGIN
    SET @new_order1 = ' ORDER BY ' + @order

    SET @new_order2 = @new_order1
    SET @new_order2 = Replace(@new_order2,'desc','')
    SET @new_order2 = Replace(@new_order2,'asc','desc')
END

SET @SqlCount = 'SELECT @RowsCount=COUNT(*),@PageCount=CEILING((COUNT(*)+0.0)/'
            + CAST(@PageSize AS VARCHAR)+') FROM ' + @Table + @new_where1

EXEC SP_EXECUTESQL @SqlCount,N'@RowsCount INT OUTPUT,@PageCount INT OUTPUT',
               @RowsCount OUTPUT,@PageCount OUTPUT

IF @PageIndex > CEILING((@RowsCount+0.0)/@PageSize)    --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数
BEGIN
    SET @PageIndex =  CEILING((@RowsCount+0.0)/@PageSize)
END

IF @PageIndex = 1
BEGIN
    IF(@BeginIndex<0)
    begin
        SET @Sql = 'SELECT TOP ' + STR(@PageSize + @BeginIndex) + ' ' + @Fields + ' FROM ' 
                   + @Table + @new_where1 + @new_order1
    end
    else
    begin
        SET @Sql = 'SELECT * FROM (SELECT TOP ' + STR(@PageSize) + ' ' + @Fields + 'FROM (SELECT TOP ' + STR(@PageSize + @BeginIndex) + ' ' + @Fields + ' FROM ' 
                   + @Table + @new_where1 + @new_order1 + ') AS #tnews_tmp1' + @new_order2 + ') AS #tnews_tmp2' + @new_order1
    end
END
ELSE
BEGIN
    IF @PageIndex = @PageCount
    BEGIN
        IF @PageIndex*@PageSize=@RowsCount
            SET @Top = @PageSize
        ELSE
            SET @Top = @RowsCount-(@PageIndex*@PageSize-@PageSize)
    END
    ELSE 
        SET @Top = @PageSize

        IF(@BeginIndex<0)
    BEGIN
        SET @sql = 'SELECT '+ @Fields +' FROM (SELECT TOP ' + STR(@Top) + ' ' + @Fields 
            + ' FROM (SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @Fields 
            + ' FROM ' +@Table + @new_where1 + @new_order1 + ') AS tmp ' + @new_order2 + ') AS tmp2 ' + @new_order1
    END
    ELSE
    BEGIN
        SET @sql = 'SELECT '+ @Fields +' FROM (SELECT TOP ' + STR(@Top) + ' ' + @Fields 
            + ' FROM (SELECT TOP ' + STR(@PageSize*@PageIndex + @BeginIndex) + ' ' + @Fields 
            + ' FROM ' +@Table + @new_where1 + @new_order1 + ') AS tmp ' + @new_order2 + ') AS tmp2 ' + @new_order1        
    END
END

--select @Sql

EXEC(@Sql)