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

Question

i want to read text from text file and save text file data into my SQL database how can i do this any idea? i am new to the c# languange

Biswa Pujarini Mohapatra · 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

Biswa Pujarini Mohapatra · Answer

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

Biswa Pujarini Mohapatra · Answer

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

Biswa Pujarini Mohapatra · Answer

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 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

Biswa Pujarini Mohapatra · Answer

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

Biswa Pujarini Mohapatra · Answer

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

Vulpes · Answer

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);

Biswa Pujarini Mohapatra · Answer

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

Biswa Pujarini Mohapatra · Answer

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

Nitin Sontakke · Answer

@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 Reddy · Answer

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 Patel · Answer

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 Ashiq · Answer

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 Ashiq · Answer

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

Ali Ashiq · Answer

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

Ali Ashiq · Answer

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 Mohapatra · Answer

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

Ali Ashiq · Answer

[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 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 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 Mohapatra · Answer

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 Ashiq · Answer

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.

Ali Ashiq · Answer

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

Biswa Pujarini Mohapatra · Answer

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 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

Ali Ashiq · Answer

Mam i am waiting for your reply

Ali Ashiq · Answer

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 Ashiq · Answer

SELECT * FROM RstProductDetails WHERE Product_Price 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 1300. i have a price in both format 2000 or 2000 so in which datatype i select for Product_price that will give me exact Product which is

Ali Ashiq · Answer

Here is new diagram

Ali Ashiq · Answer

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.

Ali Ashiq · Answer

Mam Biswa Pujarini Mohapatra i am waiting for your reply

Ali Ashiq · Answer

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 Ashiq · Answer

It will store only one line/row

Ali Ashiq · Answer

Here is my Database diagram

Ali Ashiq · Answer

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 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; }

Ali Ashiq · Answer

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

Ali Ashiq · Answer

Chicken Sausage McMuffin with Egg  280  Macdonalds Egg McMuffin250 Macdonalds Chicken Sausage McMuffin225 Macdonalds Egg Cheese McMuffin180 Macdonalds Hash Browns (Single)75 Macdonalds 2 pcs Hot Cakes240 Macdonalds 3 pcs Hot Cakes300 Macdonalds Orange/Tropical Juice Small120 Macdonalds Orange/Tropical Juice Tall160 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.