WCF Retrive the Date With

[ServiceContract]
public interface IService1
{

[OperationContract]
Employee[] GetEmployeesList();

[OperationContract]
Employees[] GetEmployeesListID(Int32 empId);
}

[DataContract]
public class Employee
{
public Int32 _employee_ID;
public string _firstName;
public string _lastName;
[DataMember]
public Int32 Employee_ID
{
get { return _employee_ID; }
set { _employee_ID = value; }
}
[DataMember]
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
[DataMember]
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
}

[DataContract]
public class Employees
{

public string _firstName;
public string _lastName;

[DataMember]
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
[DataMember]
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
}

3) Open the “Service1.svc.cs” and add the below code.

using System.Data.SqlClient;
using System.Data;

public class Service1 : IService1
{
Employee[] IService1.GetEmployeesList()
{

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString());

SqlCommand com = new SqlCommand(“select userid,firstname,lastname from Users order by userid”, con);

SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
int RCount = dt.Rows.Count;
Employee[] arrEmp = new Employee[RCount];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
arrEmp[i] = new Employee();
arrEmp[i]._employee_ID = Convert.ToInt32(dr["userid"]);
arrEmp[i]._firstName = dr["firstname"].ToString();
arrEmp[i]._lastName = dr["lastname"].ToString();
i++;

}
return arrEmp;

}

Employees[] IService1.GetEmployeesListID(Int32 empId)
{

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString());

SqlCommand com = new SqlCommand(“select firstname,lastname from Users where userid=” + empId , con);

SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
int RCount = dt.Rows.Count;
Employees[] arrEmps = new Employees[RCount];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
arrEmps[i] = new Employees();
arrEmps[i]._firstName = dr["firstname"].ToString();
arrEmps[i]._lastName = dr["lastname"].ToString();
i++;

}
return arrEmps;

}
}

4) Now add the connectionstring in web.config file

<appSettings>
<add key=”ConnectionString” value=”server=ip/servername;database=DBname;uid=usename;password=password;”/>
</appSettings>

5) add the endpoint in Web.config, i.e just below the
tag, Add client tag and add the ebdpoint, Then paste the url you have copied from the browser in endpoint “address” and Build the project.

Add endpoint in Web config

6) Run your project, you will get a page as below, Now copy the URL in the browser.

WCF Url

7) Now, Open Visual studio 2008/2010 –> New Projet, Select Visual C# –> web, then select ASP.NET Web Application(I have renamed the name as “Tester”).

8) Click on solution name and then go to “Add Service Reference” one window will open, paste the previously copied link to the address bar and press GO. Then

the service will appear in service section, then press OK. The service reference will appear in solution explorer as below:

WCF ServiceReference

9) In default.aspx page, add GridView1, below code b/w the form tags / or add the controls manually from tool box.

<asp:GridView ID=”GridView1″ runat=”server”></asp:GridView>
<asp:Label ID=”Label1″ runat=”server” Text=”Search”></asp:Label> 
<asp:TextBox ID=”TextBox3″ runat=”server”></asp:TextBox>Please enter User ID
<asp:GridView ID=”GridView2″ runat=”server”></asp:GridView>
<asp:Button ID=”Button1″ runat=”server” Text=”Click” onclick=”Button1_Click” />

10) In default.aspx.cs, add the below code,

using System.Web.UI.MobileControls;
using tester.ServiceReference1;

protected void Button1_Click(object sender, EventArgs e)
{
ServiceReference1.Service1Client objsrv = new tester.ServiceReference1.Service1Client();
objsrv.Open();

GridView1.DataSource = objsrv.GetEmployeesList();
GridView1.DataBind();
Response.Write(“<br/>”);
GridView2.DataSource = objsrv.GetEmployeesListID(Convert.ToInt32(TextBox1.Text));
GridView2.DataBind();
}

11) Now, save and build the file and run the project. If everything goes fine, then the output will be like this:

WCF output

Hope you will enjouy this, cheers 

Uploading and Downloading Word Files From Database Using ASP.NET C#

Background

Now, recently I am focusing on files related articles due to their relevance to file related operations. Now in this article I will explain how to upload Word files into a database and how to download those files from the database. This type of requirement can exist in any project such as how to upload only a .doc or .docx file resume, so because of the above requirement I have decide to write this article.

Now before creating the application, let us create a table named WordFiles in a database to store the uploaded Word files in a database table having the following fields (shown in the following image):

In the preceding table I have created four columns, they are id for the unique identity, Name for the Word  file name, type for file type and data to store the actual content of the files with a binary datatype because the content of the files are stored in bytes.

I hope you have created the same type of table.

Now  let us start to create an application to upload and download Word files step-by-step.

Now create the project  as:

1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
3. Give the Project   name such as  WordFileUploadDownload  or another as you wish and specify the location.
4. Then right-click on Solution Explorer - "Add New Item" - Default.aspx page. 
5. One File upload control, two Buttons, one label and a grid view.

Then the  <form> section of the Default aspx page looks as in the following:

 <form id="form1" runat="server">

    <div>  

   <table>

    <tr>

    <td> 

        Select File

        </td>

        <td>

        <asp:FileUpload ID="FileUpload1" runat="server" ToolTip="Select Only word File" />

        </td>

        <td> 

        <asp:Button ID="Button1" runat="server" Text="Upload" onclick="Button1_Click" />

        </td>

        <td> 

            <asp:Button ID="Button2" runat="server" Text="View Files" 

                onclick="Button2_Click" />

               </td>

        </tr> 

</table>

<table><tr><td><p><asp:Label ID="Label2" runat="server" Text="label"></asp:Label>  </p></td></tr></table>

<asp:GridView ID="GridView1" runat="server" Caption="Excel Files " 

        CaptionAlign="Top" HorizontalAlign="Justify" 

         DataKeyNames="id" onselectedindexchanged="GridView1_SelectedIndexChanged" 

        ToolTip="Word FIle DownLoad Tool" CellPadding="4" ForeColor="#333333" 

        GridLines="None">

        <RowStyle BackColor="#E3EAEB" />

        <Columns>

            <asp:CommandField ShowSelectButton="True" SelectText="Download" ControlStyle-ForeColor="Blue"/>

        </Columns>

        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />

        <HeaderStyle BackColor="Gray" Font-Bold="True" ForeColor="White" />

        <EditRowStyle BackColor="#7C6F57" />

        <AlternatingRowStyle BackColor="White" />

    </asp:GridView> 

    </div>

 </form>

Now switch to design mode and double-click on the upload button and use the following code to upload and validate that only PDF files are allowed to be uploaded.

protected void Button1_Click(object sender, EventArgs e)

    {

        Label2.Visible = true;

        string filePath = FileUpload1.PostedFile.FileName;          // getting the file path of uploaded file

        string filename1 = Path.GetFileName(filePath);               // getting the file name of uploaded file

        string ext = Path.GetExtension(filename1);                      // getting the file extension of uploaded file

        string type = String.Empty;

 if (!FileUpload1.HasFile)

        {

            Label2.Text = "Please Select File";                          //if file uploader has no file selected

        }

        else

        if (FileUpload1.HasFile)

        {

            try

            {

                                                     
                switch (ext)                                         // this switch code validate the files which allow to upload only PDF  file 

                {

                    case ".doc": 

                        type = "application/word"; 

                        break;    

                     case ".docx": 

                        type = "application/word"; 

                        break;               

                 

                }
 

                if (type != String.Empty)

                { 

                   connection();

                    Stream fs = FileUpload1.PostedFile.InputStream;

                    BinaryReader br = new BinaryReader(fs);                                 //reads the   binary files

                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);                           //counting the file length into bytes

                    query = "insert into wordFiles (Name,type,data)" + " values (@Name, @type, @Data)";   //insert query

                    com = new SqlCommand(query, con);

                    com.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename1;

                    com.Parameters.Add("@type", SqlDbType.VarChar).Value = type;

                    com.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

                    com.ExecuteNonQuery();

                    Label2.ForeColor = System.Drawing.Color.Green;

                    Label2.Text = " Word File Uploaded Successfully"; 

                }

                else

                {

                    Label2.ForeColor = System.Drawing.Color.Red; 

                    Label2.Text = "Select Only word Files  ";                              // if file is other than speified extension 

                }

            }

            catch (Exception ex)

            {

                Label2.Text = "Error: " + ex.Message.ToString(); 

            } 

        }

    }

Add the following code in the view file button click to View uploaded PDF files in GridView

protected void Button2_Click(object sender, EventArgs e)

    {

        connection();

        query = "Select *from WordFiles";

        SqlDataAdapter da = new SqlDataAdapter(query, con);

        DataSet ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds;

        GridView1.DataBind();

        con.Close();

    }

Add the following code to the Gridview selected index changed event to download the files:

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

        {

             connection();

            SqlCommand com =new SqlCommand("select Name,type,data from  WordFiles where id=@id", con);

            com.Parameters.AddWithValue("id", GridView1.SelectedRow.Cells[1].Text);

            SqlDataReader dr = com.ExecuteReader();

 

            if (dr.Read())

            {

                Response.Clear();

                Response.Buffer =true;

                Response.ContentType = dr["type"].ToString();

                Response.AddHeader("content-disposition", "attachment;filename=" + dr["Name"].ToString());     // to open file prompt Box open or Save file         

                Response.Charset = "";

                Response.Cache.SetCacheability(HttpCacheability.NoCache);

                Response.BinaryWrite((byte[])dr["data"]);

                Response.End();

            }

}

Then run the page which will look as in the following:

From the above view I am using two buttons to do the upload; one to upload the selected files to the database and view files which shows the files in a grid view which is stored in a database table.

Now run the application and select a file that is not a Word file and the result will be the error as shown in the following:

Now select the Word file, which shows the following message after being successfully uploaded:

Now click on view files details. The gridview shows the uploaded files with details as shown below. 

Now click on the download button of the gridview, the following prompt message is displayed as shown in the following image:

Then choose browse with MicroSoft Word and click on the "Ok" button. Then the file will be opened in Word format.

Summary

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.