as an example:
I have a solution that exists of several projects. I have 1 project called DataAccess.
To create the SqlCommand I use:
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;
namespace DataAccess
{
public class SQLCommands
{
public static String DefaultConnectionName()
{
return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[0].Name;
}
public static SqlCommand OpenCommand(String StoredProcedure, List<sqlparameter> sqlParameters, String ConnectionName)
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[ConnectionName ?? DefaultConnectionName()];
if (settings == null) throw new Exception("No connectionstring found");
SqlCommand cmd = new SqlCommand(StoredProcedure, new SqlConnection(settings.ConnectionString));
cmd.CommandType = CommandType.StoredProcedure;
if (null != sqlParameters)
{
CheckParameters(sqlParameters);
cmd.Parameters.AddRange(sqlParameters.ToArray());
}
cmd.CommandTimeout = 60;
cmd.UpdatedRowSource = UpdateRowSource.None;
cmd.Connection.Open();
return cmd;
}
private static void CheckParameters(List<sqlparameter> sqlParameters)
{
foreach (SqlParameter parm in sqlParameters)
{
if (null == parm.Value)
parm.Value = DBNull.Value;
}
}
public static void CloseCommand(SqlCommand sqlCommand)
{
if (null != sqlCommand && sqlCommand.Connection.State == ConnectionState.Open)
sqlCommand.Connection.Close();
}
}
}
</sqlparameter></sqlparameter>
to do a ExecuteNonQuery I have another class that uses the above class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace DataAccess
{
public class ToDataBase
{
public static Int32 InsertData(String StoredProcedure, List<sqlparameter> parms, String ConnectionName)
{
SqlCommand myCommand = null;
Int32 affectedRows = 0;
try
{
myCommand = SQLCommands.OpenCommand(StoredProcedure, parms, ConnectionName);
affectedRows = myCommand.ExecuteNonQuery();
}
catch (Exception err)
{
string error = err.ToString();
}
finally
{
SQLCommands.CloseCommand(myCommand);
}
return affectedRows;
}
}
}
</sqlparameter>
I hope this will give you a lead to your solution