Taylor Taylor - 6 months ago 30
SQL Question

How To insert data into SQL Sever with C# without using the database wizard(visual studio)

I'm having trouble inserting data into my table called "client" in my database called "vet".

I'm trying to do the connection manually as opposed to using the database wizard. I want to do it this way so that i can hide the majority of my code in a separate class.

I have a feeling I have the logic for this all wrong as I'm not making use of a data set, I was hoping someone could point me in the right direction.

This is for a school assignment and not for real world use. I have read a number of similar posts but am still unable to come to a solution.

public void CommitToDatabase()
{
using (var con = new SqlConnection(CLSDatabaseDetails.GlobalConnectionString))
{
DataTable client = new DataTable();
DataSet ds = new DataSet();
string commandString = "SELECT * FROM client";
SqlCommand cmd = new SqlCommand(commandString, con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(client);
DataRow newClientRow = ds.Tables["client"].NewRow();
newClientRow["ClientID"] = ClientID;
newClientRow["FirstName"] = FirstName;
newClientRow["LastName"] = LastName;
newClientRow["Phone"] = PhoneNumber;
newClientRow["CAddress"] = Address;
newClientRow["Email"] = Email;
newClientRow["CUsername"] = Username;
newClientRow["CPassword"] = Password;
client.Rows.Add(newClientRow);
da.Update(client);
}
}

Answer

A DataSet is a collection of DataTables. In your example, you're only referring to one table. The SqlDataAdapter.Fill method will populate either DataSet or DataTable. You're calling da.Fill(client) which is fine - it will fill the DataTable client. You could also call da.Fill(ds, "client") which will fill the DataSet ds. You can then extract your table via ds.Tables["client"]. Using a DataSet in this example is overkill, but you can show your tutor how a DataSet works.

You're filling the DataTable but then adding the new row with reference to the DataSet. But in your example, the DataSet ds has nothing in it, because you filled the DataTable not the DataSet. Try this:

DataSet ds = new DataSet();
string commandString = "SELECT TOP 0 * FROM client";
SqlCommand cmd = new SqlCommand(commandString, con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.Fill(ds, "client");
DataTable client = ds.Tables["client"];
DataRow newClientRow = client.NewRow();
... ... ... 
client.Rows.Add(newClientRow);
builder.GetInsertCommand();
da.Update(client);

If you only want to insert a row, you don't need to SELECT all the rows. If you SELECT TOP 0 * from client you'll get the column names (which you need) but without the overhead of returning all the rows.

Comments