public static SqlCommand GenerateSelectCommand(TableWrapper tblWrap, DataRow row, bool useComputedColsInWhere)
{
IList <string> whereParts = new List <string>();
if (tblWrap == null || tblWrap.Columns.Count == 0)
{
return(null);
}
SqlCommand result = new SqlCommand();
int paramCnt = 1;
for (int i = 0; i < tblWrap.Columns.Count; i++)
{
ColumnWrapper col = tblWrap.Columns[i];
//Do not include computed columns in where part
if (!useComputedColsInWhere && col.IsComputed)
{
continue;
}
object cvCurrent = row[col.Name, DataRowVersion.Original];
if (cvCurrent == null || cvCurrent.GetType() == typeof(DBNull))
{
whereParts.Add("( [" + col.Name + "] is null )");
}
else
{
result.Parameters.AddWithValue("@p" + paramCnt.ToString(), cvCurrent);
whereParts.Add("( [" + col.Name + "] = @p" + paramCnt.ToString() + " )");
paramCnt++;
}
}
string wherePart = String.Empty;
string andVal = String.Empty;
for (int i = 0; i < whereParts.Count; i++)
{
wherePart += String.IsNullOrEmpty(andVal) ? whereParts[i] : andVal + whereParts[i];
andVal = " AND ";
}
result.CommandText = "select * from [" + tblWrap.Name + "]"
+ "\n"
+ "WHERE"
+ wherePart
+ "\t";
return(result);
}
private void GetTableSchema()
{
_idCol = null;
if (_tblSchema.ConnectionParams == null)
{
_tblSchema.ConnectionParams = _connParams;
}
_tblSchema.Name = this.TableName;
_tblSchema.LoadPropsUsingName();
_tblSchema.LoadColumns();
_idCol = _tblSchema.GetIdentityColumn();
}
public static SqlCommand GenerateInsertCommand(TableWrapper tblSchema, DataRow row, string tableName)
{
IList <ColumnWrapper> validColumns = new List <ColumnWrapper>();
IList <object> currentVals = new List <object>();
//1- Lets create insert part of the command
foreach (ColumnWrapper col in tblSchema.Columns)
{
//Skip computed columns
if (col.IsComputed || col.IsIdentity)
{
continue;
}
object cvCurrent = row[col.Name, DataRowVersion.Current];
if (cvCurrent == null || cvCurrent.GetType() == typeof(DBNull))
{
continue;
}
currentVals.Add(cvCurrent);
validColumns.Add(col);
}
//Nothing to be inserted. Exit!
if (validColumns.Count == 0)
{
return(null);
}
SqlCommand result = new SqlCommand();
string colNames = "( ";
string colParams = "( ";
int paramCnt = 1;
for (int i = 0; i < validColumns.Count; i++)
{
ColumnWrapper col = validColumns[i];
if (i == 0)
{
colNames += "[" + col.Name + "]";
colParams += "@p" + paramCnt.ToString();
}
else
{
colNames += ", [" + col.Name + "]";
colParams += ", @p" + paramCnt.ToString();
}
result.Parameters.AddWithValue("@p" + paramCnt.ToString(), currentVals[i]);
paramCnt++;
}
colNames += " ) ";
colParams += " ) ";
result.CommandText = "INSERT INTO [" + tableName + "]" + colNames
+ "\n"
+ "VALUES " + colParams;
return(result);
}
public static SqlCommand GenerateUpdateCommand(TableWrapper tblSchema, DataRow row, string tableName)
{
IList <string> whereParts = new List <string>();
IList <string> updateParts = new List <string>();
if (tblSchema.Columns.Count == 0)
{
return(null);
}
SqlCommand result = new SqlCommand();
string param = String.Empty;
int paramCnt = 0;
for (int i = 0; i < tblSchema.Columns.Count; i++)
{
ColumnWrapper col = tblSchema.Columns[i];
object cvCurrent = row[col.Name, DataRowVersion.Current];
object cvOriginal = row[col.Name, DataRowVersion.Original];
if (cvOriginal == null || cvOriginal.GetType() == typeof(DBNull))
{
whereParts.Add("( [" + col.Name + "] is null )");
}
else
{
paramCnt++;
param = "@p" + paramCnt.ToString();
result.Parameters.AddWithValue(param, cvOriginal);
whereParts.Add("( [" + col.Name + "] = " + param + " )");
}
if (!col.IsComputed && !col.IsIdentity)
{
if (cvCurrent == null || cvCurrent.GetType() == typeof(DBNull))
{
updateParts.Add("[" + col.Name + "] = NULL ");
}
else
{
paramCnt++;
param = "@p" + paramCnt.ToString();
result.Parameters.AddWithValue(param, cvCurrent);
updateParts.Add("[" + col.Name + "] = " + param);
}
}
}
string wherePart = String.Empty;
string updatePart = String.Empty;
for (int i = 0; i < updateParts.Count; i++)
{
if (i == 0)
{
updatePart += updateParts[i];
}
else
{
updatePart += ", " + updateParts[i];
}
}
for (int i = 0; i < whereParts.Count; i++)
{
if (i == 0)
{
wherePart += whereParts[i];
}
else
{
wherePart += " AND " + whereParts[i];
}
}
result.CommandText = "UPDATE [" + tableName + "]"
+ "\n"
+ "SET "
+ updatePart
+ "\n"
+ "WHERE "
+ wherePart
+ "\t";
return(result);
}
public void LoadColumns( )
{
_columns.Clear();
_computedCols.Clear();
string cmdText = "";
cmdText += "declare @cmplevel int select @cmplevel = cmptlevel from master..sysdatabases where name = DB_NAME()\r\n";
cmdText += "SELECT DISTINCT dbo.syscolumns.name, dbo.systypes.name AS datatype, dbo.systypes.type,";
cmdText += " case when LOWER(dbo.systypes.name) = 'nvarchar' or LOWER(dbo.systypes.name) = 'nchar' THEN dbo.syscolumns.length / 2 ELSE dbo.syscolumns.length END 'length'";
cmdText += ", dbo.syscolumns.prec";
cmdText += ", dbo.syscolumns.scale, dbo.syscolumns.colid, CAST(dbo.syscolumns.isnullable as bit) AS isnull, COLUMNPROPERTY(dbo.syscolumns.id, dbo.syscolumns.name, 'IsIdentity') AS [identity], isNull(OBJECT_NAME(syscolumns.cdefault),'') AS defConstraint,";
cmdText += " CASE WHEN @cmplevel < 90 THEN CASE WHEN O.uid IS NULL THEN O.name ELSE USER_NAME(O.uid) + '.' + O.name END";
cmdText += " ELSE CASE WHEN O.uid IS NULL THEN (O.name) ELSE (SCHEMA_NAME(O.uid)) + '.' + O.name END END ruleBind";
cmdText += ", O.Name as ruleBindName";
cmdText += " ,dbo.syscolumns.collation, dbo.syscolumns.collationid ";
cmdText += " ,CASE ISNULL(dbo.syscolumns.iscomputed,0) WHEN 1 THEN 1 ELSE 0 END iscomputed";
cmdText += " ,sc.text as formula";
cmdText += " ,IDENT_SEED('" + this.NormalizedFullName + "') as ident_seed,IDENT_INCR('" + this.NormalizedFullName + "') as ident_increment";
cmdText += " ,CASE WHEN ISNULL(sysindexkeys.id,-1) = -1 THEN CAST(0 as bit) ELSE CAST(1 as bit) END as IsInPK ";
cmdText += " FROM dbo.syscolumns";
cmdText += " LEFT OUTER JOIN dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype";
cmdText += " LEFT OUTER JOIN dbo.sysindexes ON dbo.syscolumns.id = dbo.sysindexes.id AND (dbo.sysindexes.status & 0x800 = 0x800)";
cmdText += " LEFT OUTER JOIN dbo.sysindexkeys ON dbo.sysindexkeys.indid = sysindexes.indid AND dbo.syscolumns.colid = dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id AND COL_NAME(sysindexkeys.id, sysindexkeys.colid) = dbo.syscolumns.name";
cmdText += " LEFT OUTER JOIN dbo.sysconstraints ON dbo.syscolumns.colid = dbo.sysconstraints.colid AND dbo.syscolumns.id = dbo.sysconstraints.id AND ( dbo.sysconstraints.status & 1 = 1)";
cmdText += " LEFT OUTER JOIN sysobjects O ON syscolumns.domain = O.id";
cmdText += " LEFT OUTER JOIN syscomments sc ON syscolumns.id = sc.id and syscolumns.colid = sc.number";
cmdText += " WHERE (dbo.syscolumns.id = OBJECT_ID('" + this.NormalizedFullName + "'))";
cmdText += " ORDER BY dbo.syscolumns.colid";
string cmdDesc = "select objtype, objname, name, value ";
cmdDesc += "from ::fn_listextendedproperty(N'MS_Description', N'USER', N'dbo',N'TABLE', N'" + this.NormalizedName + "', N'COLUMN', NULL)";
string cmdDefaults = @" declare @cmplevel int select @cmplevel = cmptlevel from master..sysdatabases where name = DB_NAME()
SELECT
CASE WHEN @cmplevel < 90
THEN CASE WHEN O.uid IS NULL
THEN (O.name)
ELSE (USER_NAME(O.uid)) + '.' + (O.name)
END
ELSE CASE WHEN O.uid IS NULL
THEN (O.name)
ELSE (SCHEMA_NAME(O.uid)) + '.' + (O.name)
END
END 'objName'
, M.text
, isnull( (SELECT constid FROM sysconstraints where constid = O.id), 0) as sysconst
,O.name defName
,C.Name colName
FROM syscomments M,syscolumns C,sysobjects O
WHERE C.id=OBJECT_ID('{0}')
AND O.type='D' AND M.id=C.cdefault AND M.id=O.id";
cmdDefaults = String.Format(cmdDefaults, this.NormalizedFullName);
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
DataTable columnsDataTable = TableWrapper.ExecuteDataTable(cmdText, conn);
DataTable dscTbl = TableWrapper.ExecuteDataTable(cmdDesc, conn);
DataColumn[] dscPkCols = new DataColumn[2];
dscPkCols[0] = dscTbl.Columns["objname"];
dscPkCols[1] = dscTbl.Columns["objtype"];
dscTbl.PrimaryKey = dscPkCols;
DataTable defTbl = TableWrapper.ExecuteDataTable(cmdDefaults, conn);
DataColumn[] defPkCols = new DataColumn[1];
defPkCols[0] = defTbl.Columns["colName"];
defTbl.PrimaryKey = defPkCols;
foreach (DataRow columnRow in columnsDataTable.Rows)
{
ColumnWrapper newColumn = new ColumnWrapper();
newColumn.Name = DbNullSafeToString(columnRow["name"]);
newColumn.DataType = DbNullSafeToString(columnRow["datatype"]);
newColumn.Width = DbNullSafeToString(columnRow["length"]);
newColumn.Scale = DbNullSafeToString(columnRow["scale"]);
newColumn.IsIdentity = DbNullSafeToBool(columnRow["identity"]);
newColumn.IsComputed = DbNullSafeToBool(columnRow["iscomputed"]);
newColumn.Formula = DbNullSafeToString(columnRow["formula"]);
newColumn.DefaultConstraint = DbNullSafeToString(columnRow["defConstraint"]);
newColumn.AllowNulls = DbNullSafeToBool(columnRow["isnull"]);
newColumn.ColId = DbNullSafeToString(columnRow["colid"]);
newColumn.Precision = DbNullSafeToString(columnRow["prec"]);
newColumn.RuleBinding = DbNullSafeToString(columnRow["ruleBindName"]);
newColumn.Collation = DbNullSafeToString(columnRow["collation"]);
newColumn.CollationId = TableWrapper.IsDbValueValid(columnRow["collationid"]) ? (int)columnRow["collationid"] : -1;;
if (newColumn.IsIdentity)
{
newColumn.Seed = ((int)(TableWrapper.IsDbValueValid(columnRow["ident_seed"]) ? (decimal)columnRow["ident_seed"] : 0)).ToString();
newColumn.Increment = ((int)(TableWrapper.IsDbValueValid(columnRow["ident_increment"]) ? (decimal)columnRow["ident_increment"] : 0)).ToString();
}
//Primary key
newColumn.IsPrimaryKey = TableWrapper.IsDbValueValid(columnRow["IsInPK"]) && (bool)columnRow["IsInPK"] == true;
//Default
DataRow defRow = defTbl.Rows.Find(newColumn.Name);
if (defRow != null)
{
if (TableWrapper.IsDbValueValid(defRow["sysconst"]) && (int)defRow["sysconst"] == 0)
{
newColumn.Default = "";
newColumn.DefaultBinding = TableWrapper.IsDbValueValid(defRow["defName"]) ? (string)defRow["defName"] : String.Empty;
}
else
{
newColumn.Default = TableWrapper.IsDbValueValid(defRow["text"]) ? ((string)defRow["text"]).Trim('(', ')') : String.Empty;
newColumn.DefaultBinding = "";
}
}
_columns.Add(newColumn);
if (newColumn.IsComputed)
{
_computedCols.Add(newColumn.LowerName, newColumn);
}
}
}
}
