Tutorial to use a dll (Class Library) in a Web Application

Hi there,

I have a Class library created to return the result of a SQL query.  I want to be able to call this DLL in a Web application, whereby, I want to add a button, and when the button is clicked, the query from the DLL will be displayed on a text file in a set location on my PC.  I am very new to Web applications, so could someone help me create this web application.  The code for my Class Library is below:
using System;
using System.Text;
using System.Data.SqlClient;
using System.IO;

namespace ClassLibrary1
{
    public class Class1
    {
        public SqlDataReader getData()
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = Connection;
            SqlCommand cmd = new SqlCommand("Select Name, Surname,Address FROM Employees WHERE EmpId < 5", Conn);
            try
            {
                Conn.Open();

                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (Exception e)
            {
                return null;
            }
        }
    }

Open in new window

NerishaBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
Firstly you need to add a reference to your DLL into your Web Project. You do that by right-clicking on References in the Solution Explorer, choosing Add Reference and browsing to your DLL.

You can't save directly to the client PC from a web page, you can only stream the file and prompt to save. You would do all that with something like:
protected void Button1_Click(object sender, EventArgs e)
{
     Class1 c1 = new Class1();
     IDataReader reader = c1.getData();

     Response.Clear();
     Response.ContentType = "text/csv";
     Response.AddHeader("Content-Disposition", "attachment; filename=sample.csv");

     StringBuilder buffer = new StringBuilder();
     while (reader.Read())
     {
          Response.Write(string.Format("\"{0}\",\"{1}\"", reader.GetString(0), reader.GetString(1));
          Response.Write("\r\n");
     }

     Response.Flush();
     Response.End();
}

Open in new window

This is only a sample of the mechanics. You need to change the string.Format part to suit the data you are outputting.
0
NerishaBAuthor Commented:
Thanks, thats great.  My only issue now, is that when it runs, it gives me the option to save a .csv file but without headings.  Is there a way that I can create this .csv file with the headings too?
0
NerishaBAuthor Commented:
Also, is there maybe a way to display this information in a table on the webpage itself, instead of having it stored in a .csv file?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Carl TawnSystems and Integration DeveloperCommented:
Yes. Before the while (reader.Read()) loop you simply need to write out another line that contains your headings. Something like:
Response.Write("\"First Name\",\"Surname\"\r\n");

while (reader.Read())
{
     // etc, etc
}

Open in new window

0
Carl TawnSystems and Integration DeveloperCommented:
>> Also, is there maybe a way to display this information in a table on the webpage itself, instead of having it stored in a .csv file?

The easiest way to do that would be to add a GridView to your page and bind the repeater to it using:
     Class1 c1 = new Class1();
     IDataReader reader = c1.getData();

     YourGridView.DataSource = reader;
     YourGridView.DataBind();

Open in new window

0
NerishaBAuthor Commented:
Thank you.  I am trying to find "Gridview" on the toolbox, but I cant seem to find it.  Is it perhaps "Table"?
0
Carl TawnSystems and Integration DeveloperCommented:
It should be there listed under the Data section. Alternatively you can add a basic one manually by adding the following to your markup:
        <asp:GridView ID="Grid1" runat="server" AutoGenerateColumns="true">
        </asp:GridView>

Open in new window

0
NerishaBAuthor Commented:
Thanks, sorry for all the questions, I have never done this before, but I dragged a GridView to the page, added the following code in the source for the GridView:

<asp:GridView ID="GridView1" runat="server" DataSourceID="Test_DataSource">
        </asp:GridView>
        <asp:SqlDataSource ID="Test_DataSource" runat="server"></asp:SqlDataSource>

Then I put your code in the code behind.  It does not run though.  What am i missing?
0
NerishaBAuthor Commented:
Actually, nevermind, I seem to have it.

One more final question:  I want to add an input box so the user can input the date, so that I only pull the data for a particular date range.  How would I do this??
0
Carl TawnSystems and Integration DeveloperCommented:
Just drop a TextBox onto the field, when you click the button you can access its value. But you will need to modify the method in your class library to accept a date as a parameter because you will need to include it in your SQL query.
0
NerishaBAuthor Commented:
In other words, in my Class Library, I should have a query that looks like this:

public SqlDataReader getData()
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = Connection;
            SqlCommand cmd = new SqlCommand("Select Name, Surname,Address FROM Employees    WHERE Date =" SelDate, Conn);
 

Where would I declare SelDate?  How would I call this dll now that I have a parameter?
0
Carl TawnSystems and Integration DeveloperCommented:
You need to change the method to:
public SqlDataReader getData(DateTime selDate)
{
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = Connection;
            SqlCommand cmd = new SqlCommand("Select Name, Surname,Address FROM Employees  WHERE Date = '" + selDate.ToString("yyyy-MM-dd") + "'", Conn);
}

Open in new window

The to call it:
Class1 c1 = new Class1();

DateTime paramDate = DateTime.Parse(YourTextBox.Text);
IDataReader reader = c1.getData(paramDate);

Open in new window

You would obviously need a bit of error handling in there to make soure you have a valid date, etc, but that's the basic mechanics of it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerishaBAuthor Commented:
Thanks a million.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.