public int[] Test1()
{
int[] colIndex = new int[19];
colIndex[0] = DataExtensions.FindIndex(names, "rid");
colIndex[1] = DataExtensions.FindIndex(names, "intA");
colIndex[2] = DataExtensions.FindIndex(names, "timeA");
colIndex[3] = DataExtensions.FindIndex(names, "moneyA");
colIndex[4] = DataExtensions.FindIndex(names, "stringA");
colIndex[5] = DataExtensions.FindIndex(names, "boolA");
colIndex[6] = DataExtensions.FindIndex(names, "guidA");
colIndex[7] = DataExtensions.FindIndex(names, "intB");
colIndex[8] = DataExtensions.FindIndex(names, "moneyB");
colIndex[9] = DataExtensions.FindIndex(names, "guidB");
colIndex[13] = DataExtensions.FindIndex(names, "shortB");
colIndex[14] = DataExtensions.FindIndex(names, "charA");
colIndex[15] = DataExtensions.FindIndex(names, "charB");
colIndex[16] = DataExtensions.FindIndex(names, "img");
colIndex[17] = DataExtensions.FindIndex(names, "g2");
colIndex[18] = DataExtensions.FindIndex(names, "ts");
return(colIndex);
}
public void Test_FindIndex()
{
int x1 = DataExtensions.FindIndex(names, "intA");
Assert.AreEqual(1, x1);
int x2 = DataExtensions.FindIndex(names, "boolA");
Assert.AreEqual(5, x2);
List <string> list = names.ToList();
int x3 = DataExtensions.FindIndex(list, "timeA");
Assert.AreEqual(2, x3);
int x4 = DataExtensions.FindIndex(list, "moneyA");
Assert.AreEqual(3, x4);
}
public static List <XmlCommandItem> GetCUDCommandByTableName(string connectionString, string database, string tableName)
{
List <XmlCommandItem> commands = new List <XmlCommandItem>(3);
List <Field> fields = GetFieldsFromTable(connectionString, database, tableName);
List <string> primaryKeys = null;
using (DbContext dbContext = CreateDbContext(connectionString, database)) {
var parameter = new { TableName = tableName };
primaryKeys = dbContext.CPQuery.Create(s_QueryPrimaryKeyScript, parameter).ToScalarList <string>();
}
if (primaryKeys.Count == 0)
{
string idFieldName = (from f in fields where f.Identity select f.Name).FirstOrDefault();
if (idFieldName != null)
{
primaryKeys.Add(idFieldName);
}
}
if (primaryKeys.Count == 0)
{
string idFieldName = (from f in fields
where string.Compare(f.DataType, "timestamp", StringComparison.OrdinalIgnoreCase) == 0
select f.Name).FirstOrDefault();
if (idFieldName != null)
{
primaryKeys.Add(idFieldName);
}
}
if (primaryKeys.Count == 0)
{
string idFieldName = (from f in fields
where (f.DefaultValue != null && f.DefaultValue.IndexOf("newsequentialid()", StringComparison.OrdinalIgnoreCase) >= 0)
select f.Name).FirstOrDefault();
if (idFieldName != null)
{
primaryKeys.Add(idFieldName);
}
}
//if( primaryKeys.Count == 0 ) { // 不能启用这段代码！
// string idFieldName = (from f in fields
// where (f.DefaultValue != null && f.DefaultValue.IndexOf("newid()", StringComparison.OrdinalIgnoreCase) >= 0)
// select f.Name).FirstOrDefault();
// if( idFieldName != null )
// primaryKeys.Add(idFieldName);
//}
XmlCommandItem insertCommand = new XmlCommandItem();
insertCommand.CommandName = "Insert" + tableName.TrimPunctuation();
insertCommand.CommandType = System.Data.CommandType.Text;
StringBuilder sbInsert1 = new StringBuilder();
StringBuilder sbInsert2 = new StringBuilder();
sbInsert1.AppendFormat("\r\ninsert into [{0}] (", tableName);
sbInsert2.Append("values (");
foreach (Field f in fields)
{
if (f.Identity || f.Computed ||
string.Compare(f.DataType, "timestamp", StringComparison.OrdinalIgnoreCase) == 0 ||
(f.DefaultValue != null && f.DefaultValue.IndexOf("newsequentialid()", StringComparison.OrdinalIgnoreCase) >= 0))
{
continue;
}
sbInsert1.AppendFormat("[{0}],", f.Name);
sbInsert2.AppendFormat("@{0},", f.Name);
insertCommand.Parameters.Add(ConvertToXmlCmdParameter(f));
}
sbInsert1.Remove(sbInsert1.Length - 1, 1).AppendLine(")");
sbInsert2.Remove(sbInsert2.Length - 1, 1).AppendLine(");");
insertCommand.CommandText = sbInsert1.ToString() + sbInsert2.ToString();
commands.Add(insertCommand);
if (primaryKeys.Count > 0)
{
XmlCommandItem updateCommand = new XmlCommandItem();
updateCommand.CommandName = "Update" + tableName.TrimPunctuation();
updateCommand.CommandType = System.Data.CommandType.Text;
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("\r\nupdate [{0}] set \r\n", tableName);
foreach (Field f in fields)
{
if (f.Identity || f.Computed ||
string.Compare(f.DataType, "timestamp", StringComparison.OrdinalIgnoreCase) == 0 ||
(f.DefaultValue != null && f.DefaultValue.IndexOf("newsequentialid()", StringComparison.OrdinalIgnoreCase) >= 0))
{
continue;
}
if (DataExtensions.FindIndex(primaryKeys, f.Name) >= 0)
{
continue;
}
sbUpdate.AppendFormat("[{0}] = @{0}, ", f.Name);
updateCommand.Parameters.Add(ConvertToXmlCmdParameter(f));
}
sbUpdate.Remove(sbUpdate.Length - 2, 2).Append("\r\nWhere ");
foreach (Field f in fields)
{
if (DataExtensions.FindIndex(primaryKeys, f.Name) < 0)
{
continue;
}
sbUpdate.AppendFormat(" [{0}] = @{0} and", f.Name);
updateCommand.Parameters.Add(ConvertToXmlCmdParameter(f));
}
sbUpdate.Remove(sbUpdate.Length - 4, 4).AppendLine();
updateCommand.CommandText = sbUpdate.ToString();
commands.Add(updateCommand);
XmlCommandItem deleteCommand = new XmlCommandItem();
deleteCommand.CommandName = "Delete" + tableName.TrimPunctuation();
deleteCommand.CommandType = System.Data.CommandType.Text;
StringBuilder sbDelete = new StringBuilder();
sbDelete.AppendFormat("\r\ndelete from [{0}] where \r\n", tableName);
foreach (Field f in fields)
{
if (DataExtensions.FindIndex(primaryKeys, f.Name) < 0)
{
continue;
}
sbDelete.AppendFormat(" [{0}] = @{0} and", f.Name);
deleteCommand.Parameters.Add(ConvertToXmlCmdParameter(f));
}
sbDelete.Remove(sbDelete.Length - 4, 4).AppendLine();
deleteCommand.CommandText = sbDelete.ToString();
commands.Add(deleteCommand);
}
return(commands);
}
