/// <summary>
/// returns number of affected rows if no identity is required
/// </summary>
/// <param name="cmds"></param>
/// <param name="transactional"></param>
/// <param name="needIdentity"></param>
/// <returns></returns>
//public IDbCommand CreateCommand(CustomizedCommand cmd, bool needIdentity)
//{
// if (cmd.ColumnsData == null || cmd.ColumnsData.Count == 0) { throw new Exception("No Defined Columns"); }
// if (string.IsNullOrEmpty(cmd.ConnectionString)) { throw new Exception("Missing Connection String"); }
// if (string.IsNullOrEmpty(cmd.TableName)) { throw new Exception("Missing TableName"); }
// return CreateCommandInternal(cmd, needIdentity);
//}
public DBManager CreateCommand(CustomizedCommand cmd, bool needIdentity)
{
if (cmd.ColumnsData == null || cmd.ColumnsData.Count == 0)
{
throw new Exception("No Defined Columns");
}
if (string.IsNullOrEmpty(cmd.ConnectionString))
{
throw new Exception("Missing Connection String");
}
if (string.IsNullOrEmpty(cmd.TableName))
{
throw new Exception("Missing TableName");
}
return(CreateCommandInternal(cmd, needIdentity));
}
public DbCommand CreateCommand(CustomizedCommand customCmd, bool needIdentity)
{
if (customCmd.ColumnsData == null || customCmd.ColumnsData.Count == 0)
{
throw new Exception("No Defined Columns");
}
if (string.IsNullOrEmpty(customCmd.ConnectionString))
{
throw new Exception("Missing Connection String");
}
if (string.IsNullOrEmpty(customCmd.TableName))
{
throw new Exception("Missing TableName");
}
DbCommand cmd = _DatabaseInstance.GetSqlStringCommand("none");
// check if column has identity or auto incremental
if (customCmd.CommandType == CustomizedCommandType.New)
{
string columns = string.Empty;
string values = string.Empty;
// create parameters (no filters in insert query)
foreach (KeyValuePair <string, object> kvp in customCmd.ColumnsData)
{
DbParameter para = cmd.CreateParameter();
para.ParameterName = string.Format("@{0}", kvp.Key);
para.Value = kvp.Value == null ? DBNull.Value : kvp.Value;
cmd.Parameters.Add(para);
}
// create statement
foreach (KeyValuePair <string, object> kvp in customCmd.ColumnsData)
{
columns += string.Format("{0},", kvp.Key);
values += string.Format("@{0},", kvp.Key);
}
columns = columns.Remove(columns.Length - 1, 1);
values = values.Remove(values.Length - 1, 1);
string statement = string.Format("Insert into {0} ({1}) values ({2}) {3} ", customCmd.TableName, columns, values, needIdentity ? "Select SCOPE_IDENTITY()" : string.Empty);
cmd.CommandText = statement;
}
else if (customCmd.CommandType == CustomizedCommandType.Update)
{
string columns = string.Empty;
string filters = string.Empty;
// create parameters
foreach (KeyValuePair <string, object> kvp in customCmd.ColumnsData)
{
DbParameter para = cmd.CreateParameter();
para.ParameterName = string.Format("@{0}", kvp.Key);
para.Value = kvp.Value == null ? DBNull.Value : kvp.Value;
cmd.Parameters.Add(para);
}
foreach (KeyValuePair <string, object> kvp in customCmd.FilteredColumnsData)
{
if (!customCmd.ColumnsData.ContainsKey(kvp.Key))
{
DbParameter para = cmd.CreateParameter();
para.ParameterName = string.Format("@{0}", kvp.Key);
para.Value = kvp.Value == null ? DBNull.Value : kvp.Value;
cmd.Parameters.Add(para);
}
}
// create statement
foreach (KeyValuePair <string, object> kvp in customCmd.ColumnsData)
{
columns += string.Format("{0} = @{0},", kvp.Key);
}
foreach (KeyValuePair <string, object> kvp in customCmd.FilteredColumnsData)
{
filters += string.Format("{0} = @{0} AND ", kvp.Key);
}
columns = columns.Remove(columns.Length - 1, 1);
filters = filters.Remove(filters.Length - 4, 4);
string statement = string.Format("Update {0} set {1} {2}", customCmd.TableName, columns, filters != string.Empty ? " Where " + filters : string.Empty);
cmd.CommandText = statement;
}
else if (customCmd.CommandType == CustomizedCommandType.Delete)
{
throw new Exception("CommandGenerator : Delete Command not implemented yet.");
}
else
{
throw new Exception("Command Type Not Defined : " + customCmd.CommandType.ToString());
}
return(cmd);
}
//private IDbCommand CreateCommandInternal(CustomizedCommand cmd, bool needIdentity)
//{
// // check if column has identity or auto incremental
// //DBManager db = new DBManager(cmd.DataProvider, cmd.ConnectionString);
// SqlCommand sqlCmd = new SqlCommand();
// sqlCmd.Connection = new SqlConnection(cmd.ConnectionString);
// sqlCmd.CommandType = CommandType.Text;
// if (cmd.CommandType == CustomizedCommandType.New)
// {
// string columns = string.Empty;
// string values = string.Empty;
// //string filters = string.Empty;
// // create parameters (no filters in insert query)
// //db.CreateParameters(cmd.ColumnsData.Count + (cmd.FilteredColumnsData == null ? 0 : cmd.FilteredColumnsData.Count));
// //db.CreateParameters(cmd.ColumnsData.Count);
// //byte index = 0;
// foreach (KeyValuePair<string, object> kvp in cmd.ColumnsData)
// {
// sqlCmd.Parameters.AddWithValue(string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
// //index++;
// }
// //foreach (KeyValuePair<string, object> kvp in cmd.FilteredColumnsData)
// //{
// // if (!cmd.ColumnsData.ContainsKey(kvp.Key))
// // {
// // db.AddParameters(index, string.Format("@{0}", kvp.Key), kvp.Value);
// // index++;
// // }
// //}
// // create statement
// foreach (KeyValuePair<string, object> kvp in cmd.ColumnsData)
// {
// columns += string.Format("{0},", kvp.Key);
// values += string.Format("@{0},", kvp.Key);
// }
// //foreach (KeyValuePair<string, object> kvp in cmd.FilteredColumnsData)
// //{
// // filters += string.Format("@{0} AND", kvp.Key);
// //}
// columns = columns.Remove(columns.Length - 1, 1);
// //filters = filters.Remove(filters.Length - 4, 4);
// values = values.Remove(values.Length - 1, 1);
// string statement = string.Format("Insert into {0} ({1}) values ({2}) {3} ", cmd.TableName, columns, values, needIdentity ? "Select SCOPE_IDENTITY()" : string.Empty);
// //string statement = string.Format("Insert into {0} ({1}) values ({2}) {3} {4}", cmd.TableName, columns, values, filters != string.Empty ? " Where " + filters : string.Empty, needIdentity ? "Select SCOPE_IDENTITY()" : string.Empty);
// sqlCmd.CommandText = statement;
// }
// else if (cmd.CommandType == CustomizedCommandType.Update)
// {
// string columns = string.Empty;
// string filters = string.Empty;
// // create parameters
// //db.CreateParameters(cmd.ColumnsData.Count + (cmd.FilteredColumnsData == null ? 0 : cmd.FilteredColumnsData.Count));
// //byte index = 0;
// foreach (KeyValuePair<string, object> kvp in cmd.ColumnsData)
// {
// sqlCmd.Parameters.AddWithValue(string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
// //index++;
// }
// foreach (KeyValuePair<string, object> kvp in cmd.FilteredColumnsData)
// {
// if (!cmd.ColumnsData.ContainsKey(kvp.Key))
// {
// sqlCmd.Parameters.AddWithValue(string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
// }
// }
// // create statement
// foreach (KeyValuePair<string, object> kvp in cmd.ColumnsData)
// {
// columns += string.Format("{0} = @{0},", kvp.Key);
// }
// foreach (KeyValuePair<string, object> kvp in cmd.FilteredColumnsData)
// {
// filters += string.Format("@{0} AND ", kvp.Key);
// }
// columns = columns.Remove(columns.Length - 1, 1);
// string statement = string.Format("Update {0} set {1} {2}", cmd.TableName, columns, filters != string.Empty ? " Where " + filters : string.Empty);
// sqlCmd.CommandText = statement;
// }
// else if (cmd.CommandType == CustomizedCommandType.Delete)
// {
// throw new Exception("CommandGenerator : Delete Command not implemented yet.");
// }
// else
// {
// throw new Exception("Command Type Not Defined : " + cmd.CommandType.ToString());
// }
// return sqlCmd;
//}
private DBManager CreateCommandInternal(CustomizedCommand cmd, bool needIdentity)
{
// check if column has identity or auto incremental
DBManager db = new DBManager(cmd.DataProvider, cmd.ConnectionString);
if (cmd.CommandType == CustomizedCommandType.New)
{
string columns = string.Empty;
string values = string.Empty;
// create parameters (no filters in insert query)
db.CreateParameters(cmd.ColumnsData.Count);
byte index = 0;
foreach (KeyValuePair <string, object> kvp in cmd.ColumnsData)
{
db.AddParameters(index, string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
index++;
}
// create statement
foreach (KeyValuePair <string, object> kvp in cmd.ColumnsData)
{
columns += string.Format("{0},", kvp.Key);
values += string.Format("@{0},", kvp.Key);
}
columns = columns.Remove(columns.Length - 1, 1);
values = values.Remove(values.Length - 1, 1);
string statement = string.Format("Insert into {0} ({1}) values ({2}) {3} ", cmd.TableName, columns, values, needIdentity ? "Select SCOPE_IDENTITY()" : string.Empty);
db.GeneralCommandText = statement;
}
else if (cmd.CommandType == CustomizedCommandType.Update)
{
string columns = string.Empty;
string filters = string.Empty;
// create parameters
db.CreateParameters(cmd.ColumnsData.Count + (cmd.FilteredColumnsData == null ? 0 : cmd.FilteredColumnsData.Count));
byte index = 0;
foreach (KeyValuePair <string, object> kvp in cmd.ColumnsData)
{
db.AddParameters(index, string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
index++;
}
foreach (KeyValuePair <string, object> kvp in cmd.FilteredColumnsData)
{
if (!cmd.ColumnsData.ContainsKey(kvp.Key))
{
db.AddParameters(index, string.Format("@{0}", kvp.Key), kvp.Value == null ? DBNull.Value : kvp.Value);
index++;
}
}
// create statement
foreach (KeyValuePair <string, object> kvp in cmd.ColumnsData)
{
columns += string.Format("{0} = @{0},", kvp.Key);
}
foreach (KeyValuePair <string, object> kvp in cmd.FilteredColumnsData)
{
filters += string.Format("{0} = @{0} AND ", kvp.Key);
}
columns = columns.Remove(columns.Length - 1, 1);
filters = filters.Remove(filters.Length - 4, 4);
string statement = string.Format("Update {0} set {1} {2}", cmd.TableName, columns, filters != string.Empty ? " Where " + filters : string.Empty);
db.GeneralCommandText = statement;
}
else if (cmd.CommandType == CustomizedCommandType.Delete)
{
throw new Exception("CommandGenerator : Delete Command not implemented yet.");
}
else
{
throw new Exception("Command Type Not Defined : " + cmd.CommandType.ToString());
}
return(db);
}