日期:2014-05-18 浏览次数:20484 次
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));
T_PageNameTableAdapter adapter =new T_PageNameTableAdapter(); T_PageNameDataTable dataTable = adapter.T_PageNameTableAdapter(); //而我用这种数据集的方式来写,就没法子读到实际返回的字段,(sql语句改变了返回表结构啊)!
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(); } }