how to read text file in c# and store in database console ap

Biswa Pujarini Mohapatra12y ago

Accepted answer

here is your changed code, I missed close bracket in sql statement

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

List myValues = new List();

string line;

StreamReader file = new StreamReader(@"C:\Users\biswapm\Downloads\mcdonaldsmenu\McdonaldsMenu1.txt");

if ((line = file.ReadLine()) != null)

{

string[] fields = line.Split(',');

using (SqlConnection con = new SqlConnection(@"server=localhost;database=TestDataBase;uid=biswapm;connection timeout=30;Trusted_Connection=Yes;Integrated Security=SSPI"))

{

con.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO Products(Product_Name, Product_Price, RstName) VALUES (@Product_Name, @Product_Price, @RstName)", con);

cmd.Parameters.AddWithValue("@Product_Name", fields[0].ToString());

cmd.Parameters.AddWithValue("@Product_Price", fields[1].ToString());

cmd.Parameters.AddWithValue("@RstName", fields[2].ToString());

cmd.ExecuteNonQuery();

}

hope it helps you, let me know if you need anything else

Nitin Sontakke8y ago

@Suresh,

Why not post a fresh question?

May be post the link of this question in your question. Why just update a question which is years old?

Also your table structure. Show some expected output.

Suresh Reddy8y ago

hii guys . am facing same problem like ali faced. I need to store the data from text file to ms-sql server , but i dont even any idea about the code to store in db. So please help me to get on this code .

my text file is like this:--

M

ARVIND PHARMA DISTRIBUTORS

2-4-472/3/3 Ist Floor Ramgopal Pet

M.G. ROAD, SECUNDERABAD-3.

Sale details of SAIN MEDICAMENTS betweeen 01/10/2017 and 31/10/2017 Page - 1

------------------------------------------------------------------------------------------------

Qty Value Code Product

------------------------------------------------------------------------------------------------

AL-KAUSAR MEDICALS REIN BAZAR

----------------------------------

1 20.36 SMS07 SAZITH 250 TABLET

------------------------------------------------------------------------------------------------

1 20.36 (TOTAL)

------------------------------------------------------------------------------------------------

AL-RAHEEM MEDICAL & GENERAL STORES TALAB KATTA

---------------------------------------------------

2 98.58 SMM01 MALOFER XT TAB

------------------------------------------------------------------------------------------------

2 98.58 (TOTAL)

------------------------------------------------------------------------------------------------

AMAR MEDICAL & GENERAL STORES KUSHAIGUDA

---------------------------------------------

2 200.00 SMC12 CALSHEL K2 TABLET

------------------------------------------------------------------------------------------------

2 200.00 (TOTAL)

Ankita Patel10y ago

Mam, I have similar code , I have two fields n my table. First Name and Last Name. what if I want to sort the data first and then save into database or saving data into accessing order into database. can you explain using same code i will do changes in my code accordingly.

Waiting for your replay. Thanks.

Ali Ashiq12y ago

Mam how do i convert my web services in REST will you please guide me and the scenario is same in which i got help to you.

Ali Ashiq12y ago

Mam how do i connect my database to my default asp.net web form website please help me

Biswa Pujarini Mohapatra12y ago

please check your data and input parameters, result will come based on the input value, please run the same query on sql and see the result

Ali Ashiq12y ago

Invalid column name 'RstID'
this error occur while debugging web services, but i fount this error according to your suggested in above comments, i put break point and debug the code i will show me the line error, its really help me, but now it will show the list of all restaurant with 129 rows. 
According to the method it will show only one restaurant name which is in HotelNames table, now whats our next step

Biswa Pujarini Mohapatra12y ago

not at all, I love to do this, here is your code, yeah but I want you to learn the things by taking babystep. try to debug your code and find out the err.

[WebMethod]

public DataSet SELECTRESTAURANT(decimal amount, decimal persons)

{

decimal price = amount / persons;

DataSet result = null;

string SQL_COMMAND_TEXT = "Select RstName FROM HotelNames R(nolock) JOIN RestaurantNames rp on r.RstID = rp.RstID where rp.Price <='"+price+"'";

using (SqlConnection connection = Class1.GetConnection())

{

connection.Open();

using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))

{

using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

{

result = new DataSet();

dataAdapter.Fill(result);

}

return result;

}

public DataSet SELECTMenu(string _resturant)

{

DataSet result = null;

string SQL_COMMAND_TEXT ="Select rp.* from HotelNames Rp(nolock) join RestaurantNames r ON r.RstID = rp.RstID where r.Rst_Name ='"+_resturant+"'";

using (SqlConnection connection = Class1.GetConnection())

{

connection.Open();

using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))

{

using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

{

result = new DataSet();

dataAdapter.Fill(result);

}

return result;

}

let me know if you found any issue

Ali Ashiq12y ago

Mam i have already done with the new thread but it doesn't show any thing :'( will you please guide me further I know i am bothering you like this but i am extremely sorry for this

Biswa Pujarini Mohapatra12y ago

can you put your query in different thread. am almost done with your thing

Ali Ashiq12y ago

[WebMethod]

public DataSet SELECTRESTAURANT(decimal amount, decimal persons)

{

decimal price = amount / persons;

DataSet result = null;

const string SQL_COMMAND_TEXT =

"Select RstName FROM HotelNames R(nolock) JOIN RestaurantNames rp on r.RstID = rp.RstID where rp.Price <='@price'";

using (SqlConnection connection = Class1.GetConnection())

{

connection.Open();

using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))

{

command.Parameters.Add("@RstName", SqlDbType.VarChar);

command.Parameters.Add("@Persons", SqlDbType.NVarChar);

command.Parameters.Add("@price", SqlDbType.VarChar);

command.Parameters["@RstName"].Value = amount;

command.Parameters["@persons"].Value = persons;

command.Parameters["@price"].Value = price;

using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

{

result = new DataSet();

// dataAdapter.Fill(result);

}

return result;

}

public DataSet SELECTMenu(string SelectRestaurant)

{

DataSet result = null;

const string SQL_COMMAND_TEXT =

"Select rp.* from HotelNames Rp(nolock) join RestaurantNames r ON r.RstID = rp.RstID where r.Rst_Name <='Mcdonald'";

using (SqlConnection connection = Class1.GetConnection())

{

connection.Open();

using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))

{

using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

{

result = new DataSet();

dataAdapter.Fill(result);

}

return result;

}

}

Here is my new methods to get two methods one will return matched resturants based on product price entered by user and then user can select one resturant and it will display product details. When i debug my web services it will show nothing except empty xml

Biswa Pujarini Mohapatra12y ago

Yes you are almost done, just remove [restaurantname] column from Restaurantnames table, only rstid is sufficient. You can always get restaurantname from hotelnames table by joining with id

Ali Ashiq12y ago

Mam i going to send images of my new database, please check those images i'have take those click step by step including data in tables please let me know that i have made DB correctly.

Biswa Pujarini Mohapatra12y ago

yes this is true, Always add ID as foreign key constraint for better indexing and performance. Great work :)

Ali Ashiq12y ago

Here is my new database diagram according to your suggested comments. Is that true diagram or relation

Biswa Pujarini Mohapatra12y ago

here is your sql command

1) to get restaurant name

Select Rstname from ResturantNames R(nolock) join Resturantproducts rp

on r.rst_id = rp.rst_id where rp.price <='300'

2) to get product details based on Resturant name

Select rp.* from Resturantproducts Rp(nolock) join Resturantnames r

on r.rst_id = rp.rst_id where r.name <='Mcdonald'

Ali Ashiq12y ago

Mam i am waiting for your reply

Ali Ashiq12y ago

Yes exactly i am looking for two methods one will return matched restaurants based on product price entered by user and then user can select one restaurant and it will display product details. Now how can i design my normalize database and their relation, i have already send u database diagram. Please guide me about database design

Ali Ashiq12y ago

SELECT * FROM RstProductDetails WHERE Product_Price <='300'

when i execute this query it will show all records which is less than or greater than 300. I am shocked about this, and Product_Price (nvarchar), because when we scrapped websites through scrapper code it will store automatically in the database ans the price format on different websites is e.g "Rs 600" or "Rs 1,300".

i have a price in both format "2000" or "2,000" so in which datatype i select for Product_price that will give me exact Product which is <=300

Biswa Pujarini Mohapatra12y ago

Make Rstid as identity field and auto increment number in sql that you can easily do using property of that field.

Ali Ashiq12y ago

Here is new diagram

Ali Ashiq12y ago

My requirements is we have made a scrapper code which scrap different websites HTML in text format ans save automatically text file information (which is being scrapped from scrapper code) in database.
now the scrapped text file is have only three fields i have attached text file as well.
I have a question how could we add RstID automatically please give me idea.

Biswa Pujarini Mohapatra12y ago

first you database diagram is wrong, please add @Rstid as foreign key to product details table.

I am not so clear about your requirement.

As per my understanding you are looking for two methods one will return matched resturants based on product price entered by user and then user can select one resturant and it will display product details. is it so, please confirm

Ali Ashiq12y ago

Mam Biswa Pujarini Mohapatra i am waiting for your reply

Ali Ashiq12y ago

Here is my Database diagram and my scenario is that When a user enter number of person and amount, amount/person let suppose 4/1000=250 and now 250 is , match in Product_Price Field in RstProductDetails, and select only Restaurant where 250 is matched. Next when a user select Restaurant e.g KFC and then KFC Products details is show. Here is my [WebMethod]

Ali Ashiq12y ago

It will store only one line/row

Ali Ashiq12y ago

Here is my Database diagram

Ali Ashiq12y ago

Here is my Database diagram and my scenario is that When a user enter number of person and amount, amount/person
let suppose 4/1000=250 and now 250 is , match in Product_Price Field in RstProductDetails, and select only Restaurant where 250 is matched. Next when a user select Restaurant e.g KFC and then KFC Products details is show.
Here is my [WebMethod]

[WebMethod]

public DataSet Restaurant(string Amount, string PersonHash)

{

// DataSet result = Amount / personHash;

DataSet result = null;

const string SQL_COMMAND_TEXT = "SELECT Rst_Name FROM RstProductDetails WHERE Product_Name WHERE Product_rice<=250 = @Rst_Name";

using (SqlConnection connection = Class1.GetConnection())

{

connection.Open();

using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))

{

command.Parameters.Add("@Rst_Name", SqlDbType.NVarChar);

command.Parameters.Add("@PersonHash", SqlDbType.NVarChar);

command.Parameters["@Rst_Name"].Value = Amount;

command.Parameters["@PersonHash"].Value = PersonHash;

using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

{

result = new DataSet();

dataAdapter.Fill(result);

}

return result;

}

Vulpes12y ago

It looks like there's a closing bracket missing after @RstName:

SqlCommand cmd = new SqlCommand("INSERT INTO Products(Product_Name, Product_Price, RstName) VALUES (@Product_Name, @Product_Price, @RstName)", con);

Ali Ashiq12y ago

Incorrect syntax near '@RstName'.
this error is showing!

Biswa Pujarini Mohapatra12y ago

here is your code.

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

List myValues = new List();

string line;

StreamReader file = new StreamReader(@"C:\Users\biswapm\Downloads\mcdonaldsmenu\McdonaldsMenu1.txt");

if ((line = file.ReadLine()) != null)

{

string[] fields = line.Split(',');

using (SqlConnection con = new SqlConnection(@"server=localhost;database=TestDataBase;uid=biswa;connection timeout=30;Trusted_Connection=Yes;Integrated Security=SSPI"))

{

con.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO Products(Product_Name, Product_Price, RstName) VALUES (@Product_Name, @Product_Price, @RstName", con);

cmd.Parameters.AddWithValue("@Product_Name", fields[0].ToString());

cmd.Parameters.AddWithValue("@Product_Price", fields[1].ToString());

cmd.Parameters.AddWithValue("@RstName", fields[2].ToString());

cmd.ExecuteNonQuery();

}

Please change your connection string and table accordingly

Ali Ashiq12y ago

Chicken Sausage McMuffin with Egg, 280 , Macdonalds

Egg McMuffin,250 ,Macdonalds

Chicken Sausage McMuffin,225 ,Macdonalds

Egg Cheese McMuffin,180 ,Macdonalds

Hash Browns (Single),75 ,Macdonalds

2 pcs Hot Cakes,240 ,Macdonalds

3 pcs Hot Cakes,300 ,Macdonalds

Orange/Tropical Juice Small,120 ,Macdonalds

Orange/Tropical Juice Tall,160 ,Macdonald

Here is my text file information, and i want to insert these information in my SQL Database, Having fileds Product_Name, Product_Price, RstName
and these information seprated by Comma Separated Value, and First color insert in Product_Name, and 280 insert in Product_Price and McDonald in RstName.

Biswa Pujarini Mohapatra12y ago

using System.IO;
class Test
{
public static void Main()
{
try
{
using (StreamReader sr = new StreamReader("TestFile.txt"))
{
String line = sr.ReadToEnd();
Console.WriteLine(line);
}
}
catch (Exception e)
{
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
}
}

this is how you can read your data from textfile. Can you paste your data format in textfile how it is, so that i can give you the exact code
to insert into Db