Introduction
In today’s world most of the applications are data centric. Every form either windows or web has tons and tons of data to de displayed; the major chunk of the data is read only or for look up purpose. To get data from the database to the client,a good amount of resource are used up for establishing database connection; too many round trip calls to the server and there by increasing the burden on the backend servers and significant increase in network traffic.
. Net has many cool features for SQL server 2000 to improve the over all scalability of the applications. We will see one of the cool futures today; retrieve multiple result sets in a single SqlDataReader. This feature is available only for SQL server, why not for Oracle?, well it’s any one’s guess. Anyway the same results can be achieved even for Oracle through other means; which we will discuss some time later.
Well. It’s time to get to business. Let us open new ASP.Net and place two DropDownList and a DataGrid.
<form id="Form1" method="post" runat="server">
<asp:DropDownList id="cboCategory" runat="server"></asp:DropDownList>
<asp:DropDownList id="cboOrder" runat="server"></asp:DropDownList>
<asp:DataGrid id="DataGridProd" runat="server"></asp:DataGrid>
</form>
Don't for get to reference name space
using System.Data.SqlClient;
Let us examine the code step by step.
Step#1 : Connect to Database
Step#2 : Club all the Sql statements in to one string separated by “;” and pass into SQLCommand object
Step#3 : Execute the SqlComman and capture the results into Sqldata reader
Step#4 : Bind Data to corresponding server controls
Step#5 : Use the NextResult()method of DataReader to get the next result set.
Step#6 : Close the SqlDataReader and Database connection.
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack){GetData();}
// Put user code to initialize the page here
}
private void GetData()
{
//Step#1 : Database Connection
SqlConnection con=new SqlConnection("data source=home-pc;initial
catalog=Northwind;User id="":Password="";);
con.Open();
//Step#2 : Pass multiple sql statements
string sql="select * from categories;
select * from orders;select top 5 * from products;";
//Step#3 : Execute Command
SqlCommand cmd=new SqlCommand(sql,con);
SqlDataReader Rd = cmd.ExecuteReader();
//Step#4 : Categories
cboCategory.DataSource=Rd;
cboCategory.DataTextField="CategoryName";
cboCategory.DataValueField="CategoryID";
cboCategory.DataBind();
//Step#5 : Move to next result set
Rd.NextResult();
//Orders
cboOrder.DataSource=Rd;
cboOrder.DataTextField="ShipName";
cboOrder.DataValueField="Orderid";
cboOrder.DataBind();
//Move to next result set
Rd.NextResult();
//Products
DataGridProd.DataSource=Rd;
DataGridProd.DataBind();
//Step # 6:Close Data reader
Rd.Close();
//close connection
con.Close();
}
Note: Do not use the CommandBehavior.CloseConnection attribute with ExecuteReader() methord of SqlCommand object.This will cause an error. Each time we use the NextResult() method of SQLDataReader internally commandobject executes the next sql statement and fetches the next result set.
Conclusion
This is one of many things we have been missing for years; .net has cool stuff like this one, to cut short the development time. You can also use Dataset in the similar fashion.Whether to go for Dataset or Datareader depends on the architecture of the project and the business requirement.
分享到:
相关推荐
讲解了数据结构,演示了如何用ado.net来解决具体的数据访问问题。重点讨论了ado.net如何有效地平衡"功能的泛化"和"执行效率",以及它如何解决对扩展性、并发性和可靠性的要求。针对其他数据访问api(包括ole db,ado...
名称空间 System.Data 通用的类 System.Data.SqlClient 专用于访问SQL Server数据库的类。 System.Data.OleDb 通过OleDb接口访问其他数据库的类。
众所周知,ADO.NET相对于ADO的最大优势在于对于数据的更新修改可以在与数据源完全断开联系的情况下进行,然后再把数据更新情况传回到 数据源。这样大大减少了连接过多对于数据库服务器资源的占用。下面是我在《ADO...
ADO.NET 包含2个组件 1..NET framework提供的程序(6,7章) (1)Connection 连接 System.Data.SqlClient.SqlConnection 数据库连接: 指定连接的服务器 登陆服务器的标识 连接数据库 设置连接字符串:server=.;uid=...
1. net 的四个核心的对象 Connection 建立与特定数据源的连接 Command 对数据源执行命令 DataReader 从数据源中读取只进且只读的数据流 DataAdapter 将数据填充到DataSet中去 包含在System.Data.SqlClient命名空间中...
C# vs2017与SQL Server 2012的增删改查的简单应用实例,在控制下运行,基于System.Data.SqlClient的Sqlconnection和SqlCamand的实例应用
这几天翻遍了资料学习DataGridView,终于在CSDN上找到个实例,但是10分确实有点坑呢,我在此基础上做了修改并添加了功能,初学者很容易就能使用了。 功能:双向同步,添加,删除,全表模糊查询等,text里code,打不...
加入命名空间 using System.Data.SqlClient; 2.连接数据库 SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = “user id=sa;password=sinofindb;initial catalog=test;...
本文实例为大家分享了ADO.NET通用数据库访问类,供大家参考学习,具体内容如下 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using ...
此存储库用于试验和探索涉及ADO.NET,EF Core和与.NET数据有关的其他领域的新思想。 当前项目 SqlServer.Core(Woodstar项目) 是SQL Server的功能齐全的ADO.NET数据库提供程序。它在.NET Core和.NET Framework上...
普通的ADO.NET获取DataSet的写法如下: using System.Configuration; using System.Data; using System.Data.SqlClient; public class SQLHelper { private static readonly string ConnectionString = ...
有关更多信息,请参见 What’s New in ASP.NET and Web Development(ASP.NET 和 Web 开发中的新增功能)。 对公共语言运行时的核心改进包括:改进了 .NET Framework 本机映像的布局、选择不再对完全受信任的程序集...
欢迎来到用于SQL Server的Microsoft ADO.NET驱动程序的主页,也称为Microsoft.Data.SqlClient GitHub存储库。 Microsoft.Data.SqlClient是Microsoft SQL Server和Azure SQL数据库的数据提供程序。 现在处于通用状态...
此类的 API 扩展已添加到 SQL Server 的 .NET Framework 数据提供程序 ( System.Data.SqlClient) 中。 System.Data.SqlClient 包含封装 SQL Server .NET Framework 数据提供程序的类。SQL Server .NET Framework ...
进行ADO.NET程序开发的第一步就是引用System.Data命名空间,其中含有所有的ADO.NET类,将using指令插入在程序的顶端: using System.Data; 1. SQL Server .NET数据提供者 using System.Data.SqlClient; 2. OLE ...
很方便的.net mysql开发工具,使用上与sqlclient一模一样,很好用。
using System.Data.SqlClient; using System.Data.SqlTypes; namespace EquipmentManage.CommonControl { /// /// data 的摘要说明。 /// public class data { public string[] listvar = new string[100]...
这个示例可以一次从SQL Server以只读方式取得全部数据,它以编程方式使用所需的ADO.NET对象。
在 C# 程序的编写过程中,常常需要把一些重要的数据存储起来,这时我们就会联想到一些数据库,如微软公司提供的SQL Server数据库、Access数据库和Oracle公司提供的Oracle数据库等。...这种数据库访问技术就叫做ADO.NET
using System.Data.SqlClient; namespace UserLogin { public partial class Form1 : Form { private String userName = ""; public String UserName { get { return userName; } //set { userName = value;...