How to insert data into local database using C#

Question

I am working on my first project using local database on C#. I have searched on internet different code for inserting data, but nothing has worked for me. I am trying different code, the problem that occurs to me is the built in functions they are using doesn't show up in my code. Can someone share the authentic code for inserting, retrieving and deleting in local database ?

The recent code that I have tried, some exception is occurring in SqlCeConnection.

This is my code :

string str="Data Source=(localdb)shop_database;Initial Catalog=shop_database;Integrated Security=True";

SqlCeConnection con = new SqlCeConnection(str);          
SqlCeDataAdapter sda = new SqlCeDataAdapter();
SqlCeCommand cmd = con.CreateCommand();

cmd.CommandText = "Insert into Account_details (Account_No,Customer_name,Customer_father_name,Profession,Mobile_No,Office_Address,House_Address,CNIC,Item_name,Item_color,Item_model,Item_engine_NO,Item_chasis_NO,Cash_price,Installment_price,Advance_given,Amount_left,Monthly_Installment,Monthly_Rent,Date_of_giving,Sponsor_name,Sponsor_father_name,Sponsor_profession,Sponsor_Address,Sponsor_CNIC,Sponsor_Mobile_No) values (@Account_No,@Customer_name,@Customer_father_name,@Profession,@Mobile_No,@Office_Address,@House_Address,@CNIC,@Item_name,@Item_color,@Item_model,@Item_engine_NO,@Item_chasis_NO,@Cash_price,@Installment_price,@Advance_given,@Amount_left,@Monthly_Installment,@Monthly_Rent,@Date_of_giving,@Sponsor_name,@Sponsor_father_name,@Sponsor_profession,@Sponsor_Address,@Sponsor_CNIC,@Sponsor_Mobile_No)";

cmd.Parameters.AddWithValue("@Account_No", this.Textbox0.Text);
cmd.Parameters.AddWithValue("@Customer_name", this.Textbox1.Text);
cmd.Parameters.AddWithValue("@Customer_father_name", this.Textbox2.Text);
cmd.Parameters.AddWithValue("@Profession", this.Textbox3.Text);
cmd.Parameters.AddWithValue("@Mobile_No", this.Textbox4.Text);
cmd.Parameters.AddWithValue("@Office_Address", this.Textbox5.Text);
cmd.Parameters.AddWithValue("@House_Address", this.Textbox6.Text);
cmd.Parameters.AddWithValue("@CNIC", this.Textbox7.Text);
cmd.Parameters.AddWithValue("@Item_name", this.Textbox14.Text);
cmd.Parameters.AddWithValue("@Item_color", this.Textbox15.Text);
cmd.Parameters.AddWithValue("@Item_model", this.Textbox16.Text);
cmd.Parameters.AddWithValue("@Item_engine_NO", this.Textbox17.Text);
cmd.Parameters.AddWithValue("@Item_chasis_NO", this.Textbox18.Text);
cmd.Parameters.AddWithValue("@Cash_price", this.Textbox19.Text);
cmd.Parameters.AddWithValue("@Installment_price", this.Textbox20.Text);
cmd.Parameters.AddWithValue("@Advance_given", this.Textbox21.Text);
cmd.Parameters.AddWithValue("@Amount_left", this.Textbox25.Text);
cmd.Parameters.AddWithValue("@Monthly_Installment", this.Textbox22.Text);
cmd.Parameters.AddWithValue("@Monthly_Rent", this.Textbox23.Text);
cmd.Parameters.AddWithValue("@Date_of_giving", this.Textbox24.Text);
cmd.Parameters.AddWithValue("@Sponsor_name", this.Textbox8.Text);
cmd.Parameters.AddWithValue("@Sponsor_father_name", this.Textbox9.Text);
cmd.Parameters.AddWithValue("@Sponsor_profession", this.Textbox10.Text);
cmd.Parameters.AddWithValue("@Sponsor_Address", this.Textbox11.Text);
cmd.Parameters.AddWithValue("@Sponsor_CNIC", this.Textbox12.Text);
cmd.Parameters.AddWithValue("@Sponsor_Mobile_No", this.Textbox13.Text);

try
{
    cmd.ExecuteNonQuery();
    MessageBox.Show("Successfully saved");
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Answer 1

To edit, insert, in general interact with your database you need the class SqlCommand. First you create a connection to your database with an SqlConnection object. Then you pass the SQL statement as a string and the connection into the constructor of the SqlConnection class. Little example:

SqlConnection con = new SqlConnection("server=localhost;database=test_db;uid=root;password=yourpassword");
SqlCommand cmd = new SqlCommand("select * from your_table", con);

To retreive the data from the database you need to use the SQL Statements. For example an SQL statement is something like:

insert into my_table (value1, value2)
values("Example", "Insertion");

When you created your SqlConnection and the SqlCommand you need to open the database connection and execute the command. Wether it's a command for receiving information from the database or editing the database you use ExecuteReader() or ExecuteNonQuery(). For example when you want to receive all the Information stored in one table you use:

SqlConnection con = new SqlConnection("connection string as shown above");
SqlCommand cmd = new SqlCommand("select * from example_table", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
    Console.WriteLine(reader[<table_index or attribute Name>]);

And finally dont forget to call the close method on your SqlConnection and SqlDataReader object

Answer 2

You are probably making two mistakes:

Problem 1. Your connecting string looks like wrong. Instead of:

Data Source=(localdb)shop_database;Initial Catalog=shop_database;Integrated Security=True";

It should be:

Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=shop_database;Integrated Security=True";

Problem 2. You are not opening the connection before executing the command. Your code in the block should be like this:

try
{
    conn.Open(); // Open the connection
    cmd.ExecuteNonQuery();
    MessageBox.Show("Successfully saved");
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    conn.Close(); // Close the connection
}

As a best practice, I recommend that you use "using" block to create your connection. In that case, you don't have to explicitly close the connection and set it to null:

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        conn.Open();
        // Remaining code
    }
}
catch(Exception ex)
{
    // Manage your exception here
}