Database Insert statement Made easy

Writing Database insert, update statement is allways tedious task especially if you have large number of table fields. Even if you have a database stored procedure, you will be passing huge string as an input parameter. I bet, you may end up by debugging the code to findout the errors in sql statement. Either there is no coma, no single quots, passed values are lesser than the actual numbers etc are common problems.How do I comeout from the mess? There is no inbuilt function you can use. I will help you to have a better framework.

• I assume that you are using SQL Server and ODBC connection.
  Create a stored procedure in your SQL server database as follows:
  

CREATE PROCEDURE [dbo].[InsertRecord]@tablename varchar(25) , @statement varchar(5000), @ReturnValue int OUTPUT
AS

BEGIN execute(@statement);

if @@error<>0 begin return 0; end else begin SET @ReturnValue= IDENT_CURRENT(@tablename); return @ReturnValue; end
END

GO
//The above sql code will create a storede procedure named "InsertRecord". This has two

//input parameters and one output parameter. The first parameter will

//accept the table name. Second parameter will accept the insert statement

//(I will explain about generating the insert statement) and the third paramete will

//return the next auto generated id. So make sure that, your target table name should

//have an auto generated field.Now, this much is enough in database.

//Letus go to c# code and write a function as follows:you should have the following lines of

//code in bigining:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.Odbc;

using System.Collections.Specialized;

using System.Windows.Forms;
//In you class, declare a global bariable as follows:
NameValueCollection FieldValue=new NameValueCollection();
public void Add(string p_fieldName, string p_fieldValue, string p_dataType)

{

p_fieldValue = p_fieldValue.Replace("'", "''");

if (p_dataType == "I")

{

FieldValue.Add(p_fieldName, p_fieldValue);

}

if (p_dataType == "S" p_dataType == "D")

{

FieldValue.Add(p_fieldName,"'" +p_fieldValue +"'");

}

}

//Our next step is to prepare the sql statement from the values we have passed into

//FieldValue using Add function

private string PrepareInsertSQLStatement(string tablename)

{

string FieldNames = "INSERT INTO " + tablename +" (", FieldValues = "(";

for (int i = 0; i <= FieldValue.Count - 1; i++)

{

FieldNames += FieldValue.GetKey(i).ToString() +",";

FieldValues += (FieldValue.GetValues(FieldValue.Keys[i])[0]).ToString() + ",";

}

FieldNames = FieldNames.Remove(FieldNames.Length - 1);

FieldNames += ") VALUES";

FieldValues = FieldValues.Remove(FieldValues.Length - 1);

FieldValues += ")";
FieldValue.Clear();

return FieldNames + FieldValues;

}
public int InsertRecordStatement(string tablename)

{

try

{

string strSQLStatement = PrepareInsertSQLStatement(tablename); OdbcCommand p_Command = new OdbcCommand("{ call InsertRecord (?, ?,?)}", mydatabaseConnectionObject);

p_Command.CommandType = CommandType.StoredProcedure;

OdbcParameter prm = p_Command.Parameters.Add("@tablename", OdbcType.VarChar);

prm.Direction = ParameterDirection.Input;

prm.Value=tablename;

OdbcParameter prm1 = p_Command.Parameters.Add("@statement", OdbcType.VarChar); prm1.Direction = ParameterDirection.Input;

prm1.Value = strSQLStatement;
OdbcParameter prm2 = p_Command.Parameters.Add("@ReturnValue", OdbcType.Int);

prm2.Direction = ParameterDirection.ReturnValue;
p_Command.ExecuteNonQuery();

if (p_Command.Parameters[2].Value.ToString() != "")

{

return (int)p_Command.Parameters[2].Value;

}

else

return 0;

}

catch (OdbcException odbcexp)

{

MessageBox.Show(odbcexp.Message.ToString());

return 0;

}

}
//we are through with the code!!.//In your window form, on click of a command button, create an instance of the class you have as follows:
DataBaseClass dbclass=new DataBaseClass();
//we will use the Add function to add the fields and value
dbClass.Add("field1","value1","S");

dbClass.Add("field2","value2","S");

dbClass.Add("field3","value3","S");

dbClass.Add("field4","value4","D");

dbClass.Add("field5","value5","I");

dbClass.Add("field6","value6","S");

dbClass.Add("field7","value7","S");
//you can add as much as you want.

//Once you are through, call InsertStament function as follows:

int intReturnID=dbClass.InsertStatement("tablename");

//If the insert is successfull, then intReturnID will hold its auto generated value.

***