Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

ASP.NET Hosting - ASPHostPortal.com :: How to Bind Data to a Label From a SqlDataSource

clock April 25, 2016 21:24 by author Dan

Label controls in ASP.NET don't have a smart tag that allows you to select a data source, so at first glance, it is not easy to see how to bind a value returned from a SqlDataSource or AccessDataSource control to the label. Here's how to programmatically access the data returned by such a DataSource and apply it to a non-databound control.

To programmatically access the contents of a SqlDataSource or AccessDataSource control you need to explicitly call its Select() method. This method accepts a single input parameter of type DataSourceSelectArguments. This parameter can contain information regarding the filters to apply or the column to Order By. For example, when working with a sortable GridView, sorting a column calls the Select() method, and passes in a DataSourceSelectArguments instance with its SortExpression property set to the column name the user chose to sort by. If you don't want the DataSource to sort or filter, you pass in DataSourceSelectArguments.Empty.

Depending on the DataSourceMode of the DataSource control, one of two objects are returned when the Select() method is called. If the DataSourceMode is set to DataReader, a DataReader object is returned. The type of DataReader (SqlDataReader, OleDbDataReader, OdbcDataReader etc) that is returned depends entirely on the provider type used - in other words, whether you are using the OleDb provider, SqlClient provider etc. It has nothing to do with the type of DataSource control. The examples below both query an Access database, but one uses the AccessDataSource control, and the other uses the SqlDataSource control. Both return OleDbDataReaders, because it is the OleDbProvider library that is used for the connection.

If the DataSourceMode is set to Dataset, or not set at all (which means that the default setting of Dataset is used) the object that is returned is a DataView. A DataView is like a DataTable on steroids. It exposes methods that allow you to filter and sort data, for example, and bind it. A DataView contains a collection of DataRowView objects, which represent each row in the returned results.

So, with a DataReader, you would access the values during the DataReader.Read() operation, in very much the same way as if you are using plain ADO.NET code, whereas with the DataSet, you would need to create an object of the appropriate type - DataView, then iterate the DataRowView collection to access the values. In this, the code is remarkably similar to accessing values directly from a DataSet's table collection using plain ADO.NET.

The code below shows the contents of an aspx file, which contains two label controls, and two SqlDataSource controls. Each SqlDataSource control has its DataSource mode set to alternative values - DataSet and DataReader, and both of them have an OnSelecting event defined in which the value of the EmployeeID parameter is assigned:

<asp:Label ID="Label1" runat="server" /> <asp:Label ID="Label2" runat="server" />

<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
    DatasourceMode="DataSet"
    SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)"
    OnSelecting="SqlDataSource1_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource
    ID="SqlDataSource2"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
    DatasourceMode="DataReader"
    SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)"
    OnSelecting="SqlDataSource2_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>


The following code snippet shows the aspx.cs file contents, where the parameter values are set in the Selecting event handler. In the Page_Load method, the data returned by each of the Sql DataSource controls is accessed and a value consigned to a label. The method of access depends on the DataSource mode, but is identical for both SqlDataSource and AccessDataSource:

[C#]
protected void Page_Load(object sender, EventArgs e)
{

    DataView dvSql = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    foreach (DataRowView drvSql in dvSql)
    {
        Label1.Text = drvSql["FirstName"].ToString();
    }

    OleDbDataReader rdrSql = (OleDbDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
    while (rdrSql.Read())
    {
        Label2.Text = rdrSql["LastName"].ToString();

    }
    rdrSql.Close();
}



protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 2;
}

protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 2;
}

[VB]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

Dim dvSql As DataView =
    DirectCast(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
    For Each drvSql As DataRowView In dvSql
        Label1.Text = drvSql("FirstName").ToString()
    Next

Dim rdrSql As OleDbDataReader =
    DirectCast(SqlDataSource2.Select(DataSourceSelectArguments.Empty), OleDbDataReader)
    While rdrSql.Read()

        Label2.Text = rdrSql("LastName").ToString()
    End While
    rdrSql.Close()
End Sub

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object,
    ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("EmployeeID").Value = 2
End Sub

Protected Sub SqlDataSource2_Selecting(ByVal sender As Object,
    ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("EmployeeID").Value = 2
End Sub


When using this technique with Sql Server - or more specifically the SqlClient provider, change OleDbDataReader to SqlDataReader in the above code. Happy Coding.

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



ASP.NET Hosting - ASPHostPortal.com :: How to solve page html getting wrongly appended to file being downloaded

clock April 18, 2016 21:24 by author Dan

Today while writing a small web application where I needed to download a datatable as csv file. I faced issue of page html getting wrongly appended to file being downloaded. I was using Response.Write to download file on client machine.

Solution to my problem was easy, I just needed to add an extra line telling content-length. Below is method that I finally ended up using.

        private void DownloadDataAsCsv(DataTable dt)
        {
            string tab = "";
            StringBuilder sb = new StringBuilder();
            foreach (DataColumn dc in dt.Columns)
            {
                sb.Append(tab + dc.ColumnName);
                tab = ",";
            }
            sb.Append("\n");
            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    sb.Append(tab + dr[i].ToString());
                    tab = ",";
                }
                sb.Append("\n");
            }
            Response.ClearHeaders();
            Response.ClearContent();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=Export.csv");
            Response.AddHeader("Content-Length", sb.ToString().Length.ToString());
            Response.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in