Sometimes if a table has many columns, inserting data can be tiresome as you have to write many input parameters from your application, a smart way to handle this would be to pass the values as xml and use openxml to save data, below is an example:
Define a datatable with all the columns you need to save and add it to a Dataset, something like the one showed below
DataSet dsAddServerDetails = new DataSet();
DataTable dtServer = new DataTable(“Server”);
dtServer.Columns.Add(“Server_Name”, Type.GetType(“System.String”));
dtServer.Columns.Add(“OS”, Type.GetType(“System.String”));
dtServer.Columns.Add(“Server_Type”, Type.GetType(“System.String”));
dtServer.Columns.Add(“CreatedBy”, Type.GetType(“System.String”));
dtServer.Rows.Add(//Insert logic to add row…);
dsAddServerDetails.Tables.Add(dtServer);
//While Saving
ArrayList arrList = BusinessLayer.GetData(dsAddServerDetails);
if (arrList.Count > 0)
{
string strServerId = arrList[0].ToString();
}
//BusinessLogic and DataAccess Layer logic
public static class BusinessLayer
{
public static ArrayList GetData(DataSet ds)
{
int intRetValue;
ArrayList arrRetVal = new ArrayList();
try
{
arrRetVal = DataAccessLayer.GetData(ds);
}
catch (Exception ex)
{
throw ex; }
return arrRetVal;
}
}
public static class DataAccessLayer
{
public static ArrayList GetData(DataSet ds)
{
int intRetValue;
ArrayList arrRetVal = new ArrayList();
try
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand(“SaveServer”);
db.AddInParameter(command, “@ServerDetails”, DbType.Xml, ds.GetXml());
db.AddOutParameter(command, “@ServerId”, DbType.Int32, 4);
intRetValue = db.ExecuteNonQuery(command);
if (intRetValue == -1)
{
int intServerId = Int32.Parse(db.GetParameterValue(command, “@ServerId”).ToString());
arrRetVal.Add(intServerId);
}
}
catch (Exception ex)
{
throw ex; }
return arrRetVal;
}
}
//Stored Procedure
ALTER PROCEDURE [dbo].[SaveServer]
– Add the parameters for the stored procedure here
@ServerDetails xml,
@ServerId int OUTPUT
AS
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT,@ServerDetails
BEGIN TRANSACTION
INSERT INTO tblServer(
Server_Name,
OS,
Server_Type,
CreatedBy)
SELECT
Server_Name,
OS,
Server_Type,
CreatedBy
FROM OPENXML(@hDoc, ‘NewDataSet/Server’,2)
WITH (
Server_Name varchar(50),
OS varchar(50),
Server_Type varchar(50),
CreatedBy datetime)
SET @ServerId = @@IDENTITY
SELECT @ServerId
IF @@ERROR <>0
BEGIN
ROLLBACK TRANSACTION
RAISERROR(‘Error Inserting Server items!’,11,1) with NOWAIT, SETERROR;
RETURN
END
ELSE
BEGIN
