[Translation] Using ADO in DataGrids and DropdownLists

Original source: http://www.codeproject.com/aspnet/easyadodgrids.asp

Using ADO in DataGrids and DropDownLists

Author: knarf_scot

This is an article on the use of reusable code to bind ADO data to the control.

Introduction

ADO is a very powerful technology that reads data from the database, but it also makes people easily confused, connection data to DataGrid or other controls require some techniques and connection methods. The way I use is to develop standardized reciprocating code access databases and display data. I have written a lot of ASP.NET pages that have been displayed in DataGrid through SQL statements.

This article will describe how I use reusable code to connect ADO data and display results in DataGrid and other controls. I will also tell how to develop your own code for similar tasks.

background

This article assumes that you have knowledge of C #, SQL, ADO, and .NET controls.

I am using the Northwind database in the presentation code, but you can use any database.

Use code

Web.config

I use in Web.config to save the string you want to use in the program. If you have not done this, then you should try it. I usually use web.config to save database connection information, as this can make it more portable.

Value = "Server = localhost; uid = myuse; password = pass; database = northwind;" />

DataGrid.aspx.cs

The following makes the full code of the DataGrid.aspx page. The role of the bindgrid () function in this program enables the connection to the database and displays the result data in the DataGrid.

Using system;

Using system.collections;

Using system.componentmodel;

Using system.data;

Using system.drawing;

Using system.Web;

Using system.Web.SessionState;

Using system.Web.ui;

Using system.Web.ui.webcontrols;

Using system.Web.ui.htmlcontrols;

Using system.data.sqlclient;

Using system.configuration;

Namespace Easy_ado_binds

{

Public class Webform1: System.Web.ui.page

{

Protected system.web.ui.webcontrols.dataGrid DataGrid1;

// Get the connection string from Web.config

Public string strconnectsql =

(ConfigurationSettings.appsettings ["dsn_sql"]);

Private Void Page_Load (Object Sender, System.EventArgs E)

{

// Construct SQL string

String Sqlstring = "SELECT * from Employee";

// Call and construct bindgrid

Bindgrid (StrConnectSQL, Sqlstring, DataGrid1);

}

Private void bindgrid (String DbconnectString, String Sqlcommand,

System.Web.ui.WebControls.DataGrid DGRID) // Load the initialization page from the database

// Bind to DataGrid

{

// Create a data connection

SqlConnection Conn = New SqlConnection (dbconnectString);

// Call the SQL statement

SQLCommand Command = New Sqlcommand (SqlCommand, Conn);

// Create a Data Adapter

SqlDataAdapter Adapter = New SqldataAdapter (Command);

// Create and populate DataSet

DataSet DS = New DataSet ();

Adapter.Fill (DS);

// Fill and bind to DataGrid

DGRID.DATASOURCE = DS;

DGRID.DATABIND ();

// Close connection

CONN.CLOSE ();

}

#Region Web Form Designer Generated Code

Override protected void oninit (Eventargs E)

{

//

// Codegen: This Call is Required by The ASP.NET Web Form Designer.

//

InitializationComponent ();

Base.onit (E);

}

Private vidinitiRizeComponent ()

{

This.Load = New System.EventHandler (this.page_load);

}

#ndregion

}

}

Get SQL strings from Web.config

Allows you to take out the string you need from Web.config, is this very flexible? I use this way to specify the connection of the database, the report server, the home page default URL string, and some other global strings.

Using system.configuration;

// Get the connection string from Web.config

Public string strconnectsql = (configurationSettings.appsettings ["dsn_sql"]);

Private void bindgrid ()

At this time, the final thing did. I put these code in any page, I hope to take the data from my own database and display it with DataGrid. I don't have to write complex C # or ADO code. Just access it, through the database, SQL, DataGrid parameters, get data for me.

How to work bindgrid ()

You pass to bindgrid () a database connection, a SQL string, and a DataGrid identifier, then connect to the database, run the SQL command, display the data in the DataGrid, and finally exit the function.

Bindgrid (DB, SQL, DATAGRID) Bindgrid ("Tell me what database", "Tell me what you want to run," tell me in which DataGrid display data ")

Bindgrid input

Private void bindgrid (String DbconnectString,

String Sqlcommand, System.Web.ui.WebControls.DataGrid DGRID)

String DbconnectString: Database string SQLCOMMAND: SQL System.Web.ui.WebControls.DataGrid DGRID: DataGrid

Note: You can specify a web control for this function in C # as input. All you have to do is specify which DataGrid is what you want to use. Private void bindgrid (String DbconnectString,

String Sqlcommand, System.Web.ui.WebControls.DataGrid DGRID)

// Load the initialization page from the database

// Bind to DataGrid

{

// Create a data connection

SqlConnection Conn = New SqlConnection (dbconnectString);

// Call the SQL statement

SQLCommand Command = New Sqlcommand (SqlCommand, Conn);

// Create a Data Adapter

SqlDataAdapter Adapter = New SqldataAdapter (Command);

// Create and populate DataSet

DataSet DS = New DataSet ();

Adapter.Fill (DS);

// Fill and bind to DataGrid

DGRID.DATASOURCE = DS;

DGRID.DATABIND ();

// Close connection

CONN.CLOSE ();

}

Call bindgrid ()

Detailed description of the function bindgrid ():

Database connection string: Specify SQL strings in Web.config: Any SQL string, or even the identifier of the stored procedure DataGrid: DataGrid

Private Void Page_Load (Object Sender, System.EventArgs E)

{

// Construct SQL string

String Sqlstring = "SELECT * from Employee";

// Call and construct bindgrid

Bindgrid (StrConnectSQL, Sqlstring, DataGrid1);

}

Use multiple DataGrids

Three DataGrids are placed on the page through different SQL commands. As shown below, it is possible to call the bindgrid () with different SQL commands. So now you can use the same code to use multiple DataGrid.

// DataGrid 1

String Sqlstring1 = "Select * from Employee";

Bindgrid (StrConnectSQL, Sqlstring1, DataGrid1);

// dategrid 2

String Sqlstring2 = "Select * from customers";

Bindgrid (StrConnectSQL, SQLString2, DataGrid2);

// DataGrid3

String Sqlstring3 = "Select * from orsders";

Bindgrid (StrConnectSQL, SQLString3, DataGrid3);

Use bindlist ()

All right. Now we will use bindgrid () to use bindlist (), which can use the drop-down list in ASP.NET.

The code is slightly difficult to understand because DropDownList has more attributes:

DataTextField: Displayed in the drop-down list, that is, the user is seen. DataValuefield: Determines the value of the user's selection.

These values ​​are added to the input parameters of bindlist (), so you can run it like this:

Bindlist (DB, SQL, Text, Value, DropdownList); Using System;

Using system.collections;

Using system.componentmodel;

Using system.data;

Using system.drawing;

Using system.Web;

Using system.Web.SessionState;

Using system.Web.ui;

Using system.Web.ui.webcontrols;

Using system.Web.ui.htmlcontrols;

Using system.data.sqlclient;

Using system.configuration;

Namespace Bindlist

{

Public class Webform1: System.Web.ui.page

{

Protected system.web.ui.webcontrols.dropdownlist dropdownload1;

// Get the connection string from Web.config

Public string strconnectsql =

(ConfigurationSettings.appsettings ["dsn_sql"]);

Private Void Page_Load (Object Sender, System.EventArgs E)

{

// Create a SQL string

String Sqlstring = "SELECT EMPLOYEID, FIRSTNAME '' LastName"

"as name from employees";

String textfield = "name";

String valuefield = "EmployeeID";

Bindlist (StrConnectSQL, SQLString, TextField,

Valuefield, DropdownList1;

}

Private void Bindlist (String StrConnectsql, String Sqlstring,

String textfield, String Valuefield,

System.Web.ui.WebControls.dropdownlist Dlist)

{

SqlConnection MyConnection = New SqlConnection (StrConnectSQL);

Sqlcommand mycommand = new sqlcommand (sqlstring, myconnection);

MyConnection.open ();

DList.DataSource = MyCommand.executeReader ();

DList.DataTextField = TextField;

DList.DataValuefield = valuefield;

DList.DATABIND ();

MyConnection.Close ();

}

#Region Web Form Designer Generated Code

Override protected void oninit (Eventargs E)

{

//

// Codegen: This Call is Required by The ASP.NET Web Form Designer.

//

InitializationComponent ();

Base.onit (E);

}

///

/// Required Method for Designer Support - Do Not Modify /// The Contents of this Method with the code editor.

///

Private vidinitiRizeComponent ()

{

This.Load = New System.EventHandler (this.page_load);

}

#ndregion

}

}

interesting place

One of the benefits of this is that you can specify a web control as a function of the function as a function in ASP.NET. This really changed my coding habit, I am now developing more general reusable code.

Why use these code

This is very simple. Once you have to encode a specific control, you don't have to write again. You can use the same code again and again.

history

November 2004 V1.1