public DbCommand GetInsertCommand(object[] parameters)
{
if (TYPE != TableSchamaType.TABLE)
{
return(null);
}
if (_schama.Count <= 0)
{
return(null);
}
var sb_param = new StringBuilder();
var lst_params = new List <DbParameter>();
var items = _schama.OrderBy(itm => itm.ColumnOrdinal);
foreach (var item in items)
{
if (item.ColumnOrdinal >= parameters.Length)
{
break;
}
if (sb_param.Length > 0)
{
sb_param.Append(",");
}
var parameter = DBAccessor.CreateParameter(string.Format("COL{0}", item.ColumnOrdinal), parameters[item.ColumnOrdinal]);
sb_param.Append(parameter.ParameterName);
lst_params.Add(parameter);
}
var sql = string.Format(SQL_INSERT_1, TableName, sb_param.ToString());
return(DBAccessor.CreateCommand(sql, lst_params.ToArray()));
}
public DbCommand GetCreateCommand(string newtable = null)
{
DbCommand command = null;
var name = newtable == null ? TableName : newtable.Trim();
if (string.IsNullOrWhiteSpace(name))
{
throw new ApplicationException("table name is empty!");
}
string script = null;
if (_schama.Count > 0)
{
var columns = new StringBuilder();
var pkeys = new StringBuilder();
foreach (var item in _schama)
{
if (columns.Length > 0)
{
columns.Append(",");
}
columns.AppendLine(string.Format("[{0}] {1} {2}{3}{4}",
item.ColumnName,
item.DataType,
item.AllowDBNull ? "NULL" : "NOT NULL",
item.DefaultValue != null ? string.Format(" default('{0}')", item.DefaultValue.ToString()) : string.Empty,
item.IsAutoIncrement ? " identity(1,1)" : string.Empty));
if (item.IsPrimaryKey)
{
if (pkeys.Length > 0)
{
pkeys.Append(",");
}
pkeys.AppendLine(item.ColumnName);
}
}
var pkscript = string.Format(SQL_CONSTRAINT_PK, name, pkeys.ToString());
script = string.Format(SQL_CREATE, name, columns.ToString(), pkeys.Length > 0 ? pkscript : string.Empty);
command = DBAccessor.CreateCommand(script);
}
return(command);
}
private void LoadSchama()
{
Ready = false;
if ((TYPE == TableSchamaType.TABLE && Exists) || (TYPE == TableSchamaType.VIEW))
{
DBAccessor.RetrieveReader(DBAccessor.CreateCommand(SchamaSQL), (reader) =>
{
var schamatable = reader.GetSchemaTable();
if (schamatable != null && schamatable.Rows.Count > 0)
{
_schama.Clear();
foreach (DataRow row in schamatable.Rows)
{
var sitm = new SchamaItem(row);
sitm.SetColumnType(reader.GetFieldType(sitm.ColumnOrdinal));
_schama.Add(sitm);
}
}
});
}
Ready = _schama.Count > 0;
}
public DbCommand GetSelectCommand()
{
return(DBAccessor.CreateCommand(SchamaSQL));
}
public DbCommand GetTruncateCommand()
{
return(TYPE == TableSchamaType.TABLE ? DBAccessor.CreateCommand(string.Format(SQL_TRUNCATE, TableName)) : null);
}
