What is the correct way to Connect then Query ??

[dzr]

Below is my db connection & insert code. It's working fine but I just kind of patched it together and am confused on how/what it's doing...

I need to go --> insert into table 1, select_identity, insert into table 2 & 3...

Is there a simpler way or better order so I don't have to copy all that and repeat for each query??

I know w/ PHP you set up 1 connection then can query->execute, query->execute... on and on... But I have been reading about something like 1 connect per query...??

--------------

protected void SubmitProgram_Click(object sender, EventArgs e)
{
if (Page.IsValid == true)
{
string emailIn = (Request.Form["cpm_email"]);

string sqlConnectionString = "server=IP.xxxxx;database=dbname;uid=username;pwd=password;";
string sqlStatement = "INSERT INTO coa_admin(username,admin_level)"
+ "VALUES ('" + emailIn + "','Program Admin')";

SqlConnection dbConnection = null;
SqlCommand dbCommand = null;
SqlDataReader dbDataReader = null;

try
{
// Instantiate the Connection object...
dbConnection = new SqlConnection();
dbConnection.ConnectionString = sqlConnectionString;
dbConnection.Open();

// Instantiate the Command object...
dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandText = sqlStatement;
dbCommand.CommandType = CommandType.Text;
dbCommand.ExecuteNonQuery();
}
finally
{
// Close the DataReader...
if (dbDataReader != null)
dbDataReader.Close();

// Kill the Command object...
if (dbCommand != null)
dbCommand.Dispose();

// Close the Connection object...
if ((dbConnection != null) && (dbConnection.State == ConnectionState.Open))
dbConnection.Close();
}
}

 

[vwhite]

You can create a function that takes the SQL statement as parameter and returns the indenity value

(sorry I only know VB.NET)
e.g. Private Function RunSQLCommand(strSQL as string) as integer

the set
dbcommand.Commandtext = strSQL

and return the identity using

Dim lRecordsAffected as integer = dbcommand.ExecuteNonQuery()
If lRecordsAffected > 0 Then
Dim cmdID As SqlCommand = New SqlCommand ("SELECT @@IDENTITY", dbConnection)
intID = Convert.ToInt32(cmdID.ExecuteScalar())
cmdID.Dispose()
End If

You do not need the datareader

Also I believe closing the connection cleans up the other objects.







Vicky....

"I used to have a handle on life...then it broke