How to read Data from text file and save in database using asp.net C#

Here is an example of how to read data from a text file (in Asp.Net) and store in database(SqlServer)

I have a text file with data stored in it in a predefined format as follow:
Format:
bookid,booktit,bookaut,bookpub,bookprc,bookimg

data
1,Asp.net,Rahul,Choudhary,380,1.jpg
2,C#.net,Rahul,Choudhary,867,2.jpg
3,ADO.NET,Rahul,Choudhary,467,2.jpg
4,Java,Rahul,Choudhary,567,2.jpg
now i want to store this data in my database table


Solution

I create a table in database (SqlServer) with name tbbook which has following columns

bookid int
booktit varchar(50)
bookaut varchar(50)
bookpub varchar(50)
bookprc int
bookimg varchar(50)

Use script to create table tbbook in your SqlServer Database

CREATE TABLE tbbook
(
[bookid] [int] NULL ,
[booktit] [varchar] (50) Null,
[bookaut] [varchar] (50) Null,
[bookpub] [varchar] (50) Null,
[bookprc] [int] NULL ,
[bookimg] [varchar] (50) Null
)

Using this code you can store data from a text file of predefined structure to your database (SqlServer).

Write following code in your .aspx page

<asp:FileUpload ID="FileUpload1" runat="server" />
   
<asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click"> Uplaod File</asp:LinkButton>
<br />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="Save File Data to Database" Visible="False" />





Now write following code in your code (.cs ) file
Use these namespaces in your code file


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

//write this code below page load event



protected void LinkButton1_Click(object sender, EventArgs e)
{
//saving file uploaded file in root directory of application
FileUpload1.PostedFile.SaveAs(Server.MapPath(FileUpload1.FileName));
ViewState["file"] = FileUpload1.FileName;
FileUpload1.Visible = false;
LinkButton1.Visible = false;
Button1.Visible = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=.;database=database1;uid=sa;");//modify connect string to match yours
con.Open();
//reading file start
StreamReader reader = new StreamReader(Server.MapPath(ViewState["file"].ToString()));
string line = string.Empty;
string[] lines = new string[6];//array to store values read from text file
while ((line = reader.ReadLine()) != null)//reading file line by line
{
lines = line.Split(',');//spliting each line by comma sign
//saving into database
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "insert into tbbook values(@id,@title,@aut,@pub,@prc,@img)";
cmd.Connection = con;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(lines[0]);
cmd.Parameters.Add("@title", SqlDbType.VarChar, 50).Value = lines[1];
cmd.Parameters.Add("@aut", SqlDbType.VarChar, 50).Value = lines[2];
cmd.Parameters.Add("@pub", SqlDbType.VarChar, 50).Value = lines[3];
cmd.Parameters.Add("@prc", SqlDbType.Int).Value = Convert.ToInt32(lines[4]);
cmd.Parameters.Add("@img", SqlDbType.VarChar, 50).Value = lines[5];
cmd.ExecuteNonQuery();
}
Response.Write("Record Saved");
//reading file end
con.Close();//closing connection to database
}

Try it!

no fear choudhary is here, Saturday, June 12, 2010