一:概述(所有遇到的数据库的相关操作都是用存储过程)
(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文件不存在");
}
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];
}
{
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;
}
{
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;
}
{
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
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 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
页面调用方法
代码1 // 在此处放置用户代码以初始化页面
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();
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)
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)