一:概述(所有遇到的数据库的相关操作都是用存储过程)
(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];
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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查询语句
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
{
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更新(删除,添加)语句
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
{
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);其他一样,如
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
{
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类:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
页面调用方法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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();
存储过程代码(举例):
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 )