If you use SSMS (SQL-Server Management Studio which is free) to create your INSERT INTO statement by right clicking the desired table, select "script table as", select "INSERT To" to a new query window we get this (using a table named Customers).
INSERT INTO [dbo].[Customer]
([FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[ZipCode]
,[AccountNumber]
,[JoinDate])
VALUES
(<FirstName, nvarchar(max),>
,<LastName, nvarchar(max),>
,<Address, nvarchar(max),>
,<City, nvarchar(max),>
,<State, nvarchar(max),>
,<ZipCode, nvarchar(max),>
,<AccountNumber, nvarchar(max),>
,<JoinDate, datetime2(7),>)
Now change the VALUES section by using a DECLARE for each value.
DECLARE @FirstName nvarchar(max)
DECLARE @LastName nvarchar(max)
DECLARE @Address nvarchar(max)
DECLARE @City nvarchar(max)
DECLARE @State nvarchar(max)
DECLARE @ZipCode nvarchar(max)
INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode)
Next, create a class rather than placing data operations into Program.cs with a method specific to adding a new record (the following still uses Customers table).
Full source where the following code comes from.
An alternate to cmd.Parameters.AddWithValue is cmd.Parameters.Add which provides fine tuning the type of the parameter.
The alternate to getting the new primary key if needed is to add a semi-colon to the end of the INSERT INTO and adding SELECT CAST(scope_identity() AS int); then use Convert.ToInt32(cmd.ExecuteScalar()) to get the new key.
So after testing with SSMS simply paste the query into a string variable and if this does not work there is something else going on.
public bool AddCustomer(string FirstName, string LastName, string Address, string City, string State, string ZipCode, ref int NewPrimaryKeyValue)
{
bool success = false;
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText =
"INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) " +
"VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode)";
try
{
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Address", Address);
cmd.Parameters.AddWithValue("@City", City);
cmd.Parameters.AddWithValue("@State", State);
cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
cn.Open();
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
cmd.CommandText = "Select @@Identity";
NewPrimaryKeyValue = Convert.ToInt32(cmd.ExecuteScalar());
success = true;
}
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
NewPrimaryKeyValue = -1;
success = false;
}
}
}
return success;
}
Calling the above method.
You can also validate column names using the following (still keeping with Customer table)
SELECT ORDINAL_POSITION,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customer'
AND TABLE_SCHEMA = 'dbo';
Results
1,id,int
2,FirstName,nvarchar
3,LastName,nvarchar
4,Address,nvarchar
5,City,nvarchar
6,State,nvarchar
7,ZipCode,nvarchar
8,AccountNumber,nvarchar
9,JoinDate,datetime2
Edit
Another option is to create a class which represents data to be inserted e.g.
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public string AccountNumber { get; set; }
public DateTime? JoinDate { get; set; }
}
Then here we use the values passed. Note, in this version cmd.Parameters.AddWithValue is replaced with cmd.Parameters.Add and the query to get the new primary key is appended after the INSERT INTO separated by a semi-colon.
To call create an instance of the Customer class, populate properties and call the method.
public bool AddCustomer(Customer customer)
{
bool success = false;
using (var cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText =
"INSERT INTO Customer (FirstName,LastName,[Address],City,[State],ZipCode) " + // insert
"VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode);" + // insert
"SELECT CAST(scope_identity() AS int);"; // get new primary key
try
{
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar))
.Value = customer.FirstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar))
.Value = customer.LastName;
cmd.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar))
.Value = customer.Address;
cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar))
.Value = customer.City;
cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar))
.Value = customer.State;
cmd.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.NVarChar))
.Value = customer.ZipCode;
cn.Open();
customer.Id = Convert.ToInt32(cmd.ExecuteScalar());
success = true;
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
customer.Id = -1;
success = false;
}
}
}
return success;
}
