up vote 1 down vote favorite

I am developing a web template with ASP.NET using C#. My connection string is:

<connectionStrings>
<add name="NorthwindConnectionString" 
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SecurityTutorials.mdf;Integrated Security=True;User Instance=True" 
providerName="System.Data.SqlClient"/>
</connectionStrings>

and the access of the connection string is as below:

string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

my SqlDataSource is as follows:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
 SelectCommand="SELECT [ProductID], [ProductName], [Discontinued] FROM [Alphabetical list of products]"
 InsertCommand = "INSERT INTO [Alphabetical list of products] (ProductID, ProductName, Discontinued)VALUES(@ProductID,@ProductName,@Discontinued)">
<InsertParameters>
<asp:Parameter Name="ProductID" Type="String" />
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="Discontinued" Type="String" />
</InsertParameters> 
</asp:SqlDataSource>

I have put a ListView on the page to display the data. Now how can I access the data in the database and how can I retrieve the data and show it inside the page using code behind?

As you know by using <%# Eval("ProductName") %> inside the page all data are accessible. For example, I have a column ProductName, I want to get the data in this column, do some reformatting and pass it to the page form code behind, and also write the SqlDataSource at the code behind.

share|improve this question
feedback

2 Answers

up vote 0 down vote accepted

I bet there are more options than this one, but this is how I would've done it. Cut out the SqlDataSource in the page and fetch and bind the data from the code:

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString)
{
  var selectCommand = new SqlCommand("SELECT ...");

  selectCommand.CommandType = CommandType.Text;
  selectCommand.Connection = connection;

  var dataAdapter = new SqlDataAdapter()
  dataAdapter.SelectCommand = selectCommand

  var dataSet = new DataSet();

  connection.Open()
  dataAdapter.Fill(dataSet, "myDataSet");
  connection.Close()

  // now, the dataSet.Tables[0] is a DataTable with your data in it. You can add, edit or remove data in this table before binding it to the ListView

  myListView.DataSource = dataSet;
  myListView.DataBind();
}
In this example, you'll have to write separate code for insertion of data. If dealing with ASP.NET, I write my code like this on purpose, since ASP.NET applications are stateless and thus don't remember any state or data (other than what's stored in Session or ViewState)

share|improve this answer
Dear KBoek, appreciate for the answer it was really helpful. but could you please provide me a code for add, update, and delete. as i'm new in this field. many thanks – F Sh Nov 14 '11 at 1:15
feedback
up vote 0 down vote

As an alternative, you could hook onto the ItemDataBound event of the ListView. See the example code in the MSDN Library: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listview.itemdatabound.aspx

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.