cs文件生成SqlDataSource,让aspx看着更清爽些
做项目的时候,
常常写的是SqlDataSource 在aspx 文件里满天飞
看着一堆一推的,自己都觉得难受,
之后写了个在cs中生成的,看着就舒服多了
不说别的了,上代码
1/*----------------------------------------------------------------------------
2 * 代码作者:ATPKING来自cnblogs.com
3 * 版本号码:SqlCon 0.1
4 * 功能介绍:在一般的.net开发中,我们经常需要把SqldataSource拖到aspx里面,
5 * 之后看aspx页面,就觉得特别的乱,也不利于三层结构的实现,于是在
6 * cs文件中生成SqlDataSource,提供给数据显示控件链接。
7 * 使用方法:放在项目文件夹App_Code下即可。
8 * 完成时间:2008-4-20
9 * 版权声明:引用时请将此注释保留,代码可以任意拷贝及其修改。
10 * 附送内容:理智爱国,抵制家乐福最好办法就是不去家乐福购物,而不是去店门口闹
11 * --------------------------------------------------------------------------*/
12using System;
13using System.Data;
14using System.Configuration;
15using System.Web;
16using System.Web.Security;
17using System.Web.UI;
18using System.Web.UI.WebControls;
19using System.Web.UI.WebControls.WebParts;
20using System.Web.UI.HtmlControls;
21using System.Data.Sql;
22using System.Data.SqlClient;
23
24/// <summary>
25/// 数据库连接相关
26/// </summary>
27public class SqlCon
28{
29 private string _sqlConnStr;//链接字符串
30 private SqlConnection _conn;//链接数据库控件
31
32
33 public string SqlConnStr//对外显示的连接字符串
34 {
35 set
36 {
37 _sqlConnStr = value;
38 }
39 get
40 {
41 return _sqlConnStr;
42 }
43 }
44 /// <summary>
45 /// 链接到web.config 的第一个连接字符串
46 /// </summary>
47 public SqlCon()
48 {
49 _sqlConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
50 _conn = new SqlConnection(_sqlConnStr);
51 }
52 /// <summary>
53 /// 链接到web.config 的制定的链接字符串
54 /// </summary>
55 /// <param name="connectName">链接名</param>
56 public SqlCon(string connectName)
57 {
58 _sqlConnStr = ConfigurationManager.ConnectionStrings[connectName].ConnectionString;
59 _conn = new SqlConnection(_sqlConnStr);
60 }
61 /// <summary>
62 /// 将select语句实行并返回一个table
63 /// </summary>
64 /// <param name="sqlStr">select 语句</param>
65 /// <returns>执行select后的一个table</returns>
66 public DataTable tableFromSql(string sqlStr)
67 {
68 if (_conn != null)
69 {
70 _conn.Open();
71 }
72 else
73 {
74 throw new Exception("数据库链接未正常工作");
75 }
76 SqlCommand _comm = new SqlCommand(sqlStr, _conn);
77 DataSet _ds = new DataSet();
78 SqlDataAdapter _da = new SqlDataAdapter(sqlStr, _conn);
79 _da.Fill(_ds);
80 DataTable dt = _ds.Tables[0];
81 return dt;
82 }
83
84 /// <summary>
85 /// 返回一个DataSource,它的链接为类的链接字符串,语句为参数语句
86 /// </summary>
87 /// <param name="sqlStr">slecet语句</param>
88 /// <returns>一个dataSource</returns>
89 public SqlDataSource dataSourceFromSql(string sqlStr)
90 {
91 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
92 return ds;
93 }
94
95 /// <summary>
96 /// 返回一个带 QueryString参数的SqldataSource
97 /// </summary>
98 /// <param name="sqlStr">select_sql语句,注意要加类似"where 字段名=@字段名"</param>
99 /// <param name="colid">where语句中的字段名</param>
100 /// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
101 /// <returns>SqldataSource</returns>
102 public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid)
103 {
104 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
105 QueryStringParameter pa = new QueryStringParameter(colid, paraid);
106 ds.SelectParameters.Add(pa);
107 return ds;
108 }
109 /// <summary>
110 /// 返回一个带 QueryString参数的SqldataSource
111 /// </summary>
112 /// <param name="sqlStr">select_sql语句,注意要加类似"where 字段名=@字段名"</param>
113 /// <param name="colid">where语句中的字段名</param>
114 /// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
115 /// <param name="defaultQueryString">默认的值</param>
116 /// <returns>SqldataSource</returns>
117 public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid,string defaultQueryString)
118 {
119 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
120 QueryStringParameter pa = new QueryStringParameter(colid, paraid);
121 pa.DefaultValue = defaultQueryString;
122 ds.SelectParameters.Add(pa);
123 return ds;
124 }
125
126 /// <summary>
127 /// 返回一个支持分页的PageDataSource
128 /// </summary>
129 /// <param name="sqlStr">Select_sql语句</param>
130 /// <returns>PageDataSource</returns>
131 public PagedDataSource pagedSourceFromSql(string sqlStr)
132 {
133 PagedDataSource objPds = new PagedDataSource();
134 SqlDataSource SqlDataSource1 = this.dataSourceFromSql(sqlStr);
135 objPds.DataSource = SqlDataSource1.Select(DataSourceSelectArguments.Empty);
136
137 objPds.AllowPaging = true;
138 objPds.PageSize = 0;
139 objPds.CurrentPageIndex = 0;
140 return objPds;
141 }
142}
143
144
2 * 代码作者:ATPKING来自cnblogs.com
3 * 版本号码:SqlCon 0.1
4 * 功能介绍:在一般的.net开发中,我们经常需要把SqldataSource拖到aspx里面,
5 * 之后看aspx页面,就觉得特别的乱,也不利于三层结构的实现,于是在
6 * cs文件中生成SqlDataSource,提供给数据显示控件链接。
7 * 使用方法:放在项目文件夹App_Code下即可。
8 * 完成时间:2008-4-20
9 * 版权声明:引用时请将此注释保留,代码可以任意拷贝及其修改。
10 * 附送内容:理智爱国,抵制家乐福最好办法就是不去家乐福购物,而不是去店门口闹
11 * --------------------------------------------------------------------------*/
12using System;
13using System.Data;
14using System.Configuration;
15using System.Web;
16using System.Web.Security;
17using System.Web.UI;
18using System.Web.UI.WebControls;
19using System.Web.UI.WebControls.WebParts;
20using System.Web.UI.HtmlControls;
21using System.Data.Sql;
22using System.Data.SqlClient;
23
24/// <summary>
25/// 数据库连接相关
26/// </summary>
27public class SqlCon
28{
29 private string _sqlConnStr;//链接字符串
30 private SqlConnection _conn;//链接数据库控件
31
32
33 public string SqlConnStr//对外显示的连接字符串
34 {
35 set
36 {
37 _sqlConnStr = value;
38 }
39 get
40 {
41 return _sqlConnStr;
42 }
43 }
44 /// <summary>
45 /// 链接到web.config 的第一个连接字符串
46 /// </summary>
47 public SqlCon()
48 {
49 _sqlConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
50 _conn = new SqlConnection(_sqlConnStr);
51 }
52 /// <summary>
53 /// 链接到web.config 的制定的链接字符串
54 /// </summary>
55 /// <param name="connectName">链接名</param>
56 public SqlCon(string connectName)
57 {
58 _sqlConnStr = ConfigurationManager.ConnectionStrings[connectName].ConnectionString;
59 _conn = new SqlConnection(_sqlConnStr);
60 }
61 /// <summary>
62 /// 将select语句实行并返回一个table
63 /// </summary>
64 /// <param name="sqlStr">select 语句</param>
65 /// <returns>执行select后的一个table</returns>
66 public DataTable tableFromSql(string sqlStr)
67 {
68 if (_conn != null)
69 {
70 _conn.Open();
71 }
72 else
73 {
74 throw new Exception("数据库链接未正常工作");
75 }
76 SqlCommand _comm = new SqlCommand(sqlStr, _conn);
77 DataSet _ds = new DataSet();
78 SqlDataAdapter _da = new SqlDataAdapter(sqlStr, _conn);
79 _da.Fill(_ds);
80 DataTable dt = _ds.Tables[0];
81 return dt;
82 }
83
84 /// <summary>
85 /// 返回一个DataSource,它的链接为类的链接字符串,语句为参数语句
86 /// </summary>
87 /// <param name="sqlStr">slecet语句</param>
88 /// <returns>一个dataSource</returns>
89 public SqlDataSource dataSourceFromSql(string sqlStr)
90 {
91 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
92 return ds;
93 }
94
95 /// <summary>
96 /// 返回一个带 QueryString参数的SqldataSource
97 /// </summary>
98 /// <param name="sqlStr">select_sql语句,注意要加类似"where 字段名=@字段名"</param>
99 /// <param name="colid">where语句中的字段名</param>
100 /// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
101 /// <returns>SqldataSource</returns>
102 public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid)
103 {
104 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
105 QueryStringParameter pa = new QueryStringParameter(colid, paraid);
106 ds.SelectParameters.Add(pa);
107 return ds;
108 }
109 /// <summary>
110 /// 返回一个带 QueryString参数的SqldataSource
111 /// </summary>
112 /// <param name="sqlStr">select_sql语句,注意要加类似"where 字段名=@字段名"</param>
113 /// <param name="colid">where语句中的字段名</param>
114 /// <param name="paraid">链接中类似aspx?id=1 那个地的?后面的字段</param>
115 /// <param name="defaultQueryString">默认的值</param>
116 /// <returns>SqldataSource</returns>
117 public SqlDataSource dataSourceFromSql(string sqlStr, string colid, string paraid,string defaultQueryString)
118 {
119 SqlDataSource ds = new SqlDataSource(_sqlConnStr, sqlStr);
120 QueryStringParameter pa = new QueryStringParameter(colid, paraid);
121 pa.DefaultValue = defaultQueryString;
122 ds.SelectParameters.Add(pa);
123 return ds;
124 }
125
126 /// <summary>
127 /// 返回一个支持分页的PageDataSource
128 /// </summary>
129 /// <param name="sqlStr">Select_sql语句</param>
130 /// <returns>PageDataSource</returns>
131 public PagedDataSource pagedSourceFromSql(string sqlStr)
132 {
133 PagedDataSource objPds = new PagedDataSource();
134 SqlDataSource SqlDataSource1 = this.dataSourceFromSql(sqlStr);
135 objPds.DataSource = SqlDataSource1.Select(DataSourceSelectArguments.Empty);
136
137 objPds.AllowPaging = true;
138 objPds.PageSize = 0;
139 objPds.CurrentPageIndex = 0;
140 return objPds;
141 }
142}
143
144