请教， 快速写代码[ado.net]

请问， 快速写代码[ado.net]
大家看下面这条sql 语句：

SQL code

select Id,pageName,text from(  select Id , FPageName as 'pageName', FContent as 'text', Row_Number() over(order by Id asc) as 'rn'   FROM dbo.T_PageName) as Tab where (Tab.rn >= @startRowIndex and Tab.rn< (@startRowIndex +@maximumRows));

[因要手动分页的需要，才写成如下的，这样，就改变了返回dataTable 的结构了，而vs的数据集控件不支持啊！]
［还有 Row_Number() 这个MS-SQL 2005才出的函数，数据集也不支持解释啊！］
[如果我用
 

C# code

 T_PageNameTableAdapter adapter =new T_PageNameTableAdapter();   T_PageNameDataTable dataTable =  adapter.T_PageNameTableAdapter();//而我用这种数据集的方式来写，就没法子读到实际返回的字段，（sql语句改变了返回表结构啊）！

]
//所以，我只好用最源始的写法了！

C# code

Dictionary<string, object> distionary = new Dictionary<string, object>();AppDomain.CurrentDomain.SetData("DataDirectory", @"D:\...\mdf文件的path");//数据库文件的路径string DataSource = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\xxx.mdf;Integrated Security=True;User Instance=True";using (SqlConnection sqlConn = new SqlConnection()){sqlConn.ConnectionString = DataSource;sqlConn.Open();string sqlStr = @"select Id,pageName,text from(select Id , FPageName as 'pageName', FContent as 'text', Row_Number() over(order by Id asc) as 'rn' FROM dbo.T_PageName) as Tab where (Tab.rn >= @startRowIndex and Tab.rn< (@startRowIndex +@maximumRows));";//sql 命令try{    using (SqlCommand comm = new SqlCommand(sqlStr, sqlConn))//    {                comm.CommandText = sqlStr;        comm.Parameters.Add(new SqlParameter("startRowIndex", startRowIndex));        comm.Parameters.Add(new SqlParameter("maximumRows", maximumRows));        using (SqlDataReader reader = comm.ExecuteReader())        {            while (reader.Read())            {                //下面这个messageBox 测试用的。要del它//MessageBox.Show((string)reader.GetString(reader.GetOrdinal("pageName")), (string)reader.GetString(reader.GetOrdinal("text")));                Dictionary<string, object> noteDic = new Dictionary<string, object>(); ;                noteDic.Add("id", (long)reader.GetSqlInt64(reader.GetOrdinal("Id")));                noteDic.Add("pageName", (string)reader.GetString(reader.GetOrdinal("pageName")));                noteDic.Add("text", (string)reader.GetString(reader.GetOrdinal("text")));                distionary.Add(reader.GetSqlInt64(reader.GetOrdinal("Id")).ToString(), noteDic);            }        }    }}catch (SqlException ex){    //异常信信显示    string ErrorInfo = "\n数据库执行出错了:\n";    ErrorInfo += "\n信息" + ex.Errors;    ErrorInfo += "\n类弄" + ex.Number;    ErrorInfo += "\n名称" + ex.Source;    ErrorInfo += "\n行号" + ex.LineNumber;    ErrorInfo += "\n详细" + ex.Message;    MessageBox.Show(ErrorInfo, "数据库执行出错了");//using System.Windows.Forms;}finally{ //关闭数据库连接    if (sqlConn.State == ConnectionState.Open)    {        sqlConn.Close();        sqlConn.Dispose();    }}

//这种写法太原始了，不想每次都自已这样来写啊，
大家提点建意吧，谢谢！

[解决办法]
数据集的Provider选2005+就可以了，
[解决办法]
可以用一下SQLHelper
[解决办法]
自己总结一套方法，写几个函数，能把代码重用起来就行了。

查看更多 下一篇