Here's 2 sample ways to do it (I used two tables, each of which have a PK and either an int or an nvarchar column):
1) Have several INSERT commands that, when the form is submitted, each pick out their own data from the form and submit them. The code would look something like this:
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack) // form submitted
{
SqlConnection sc = null;
SqlCommand command = null;
try
{
sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
sc.Open();
string query1 = String.Format(@"INSERT INTO Numbers(number) VALUES({0})",
numberInput.Text);
command = new SqlCommand(query1, sc);
command.ExecuteNonQuery();
string query2 = String.Format(@"INSERT INTO Strings(string) VALUES('{0}')",
stringInput.Text);
command = new SqlCommand(query2, sc);
command.ExecuteNonQuery();
}
finally // clean up
{
if(command != null)
command.Dispose();
if(sc != null)
sc.Close();
}
}
}
2) Have a stored procedure in the database that does all the inserts for you. The code would look kind of like this:
C#:
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack) // form submitted
{
SqlConnection sc = null;
SqlCommand command = null;
try
{
sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
sc.Open();
command = new SqlCommand("InsertData", sc);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@number", numberInput.Text);
command.Parameters.AddWithValue("@string", stringInput.Text);
command.ExecuteNonQuery();
}
finally // clean up
{
if(command != null)
command.Dispose();
if(sc != null)
sc.Close();
}
}
}SQL:
CREATE PROCEDURE dbo.InsertData
(
@number INT,
@string NVARCHAR(50)
)
AS
INSERT INTO Numbers(number)
VALUES(@number);
INSERT INTO Strings(string)
VALUES(@string);
RETURN
Note that I didn't validate my data in either of these. Also, the connection string should be in web.config, but I was too lazy to add it. Also note that using a SProc (stored procedure) requires EXECUTE permissions for your database, which, while you probably have it, isn't always the case. The sproc only requires one query to the database though, so it's better peforming; it's also "more secure" but validation is always a good idea (better yet, lack thereof is always a bad idea).
Oh, and for the auto increment property, MSSQL (aka T-SQL) uses the IDENTITY(start, increment) property in the column definition. If you're using Visual Studio, you can also open the table definition, and for the column properties for your PK set the identity property as well.
Another note about my code (especially if you're new to ASP.NET): the condition at the beginning checks to make sure the page is being reached from a form submission. It doesn't necessarily validate that it's from the same page (you can postback to a different URL), and I didn't code it to reset the form.
Hope this gets you started :)
