private static void AddContactWithInsertStatement(string connectionString)
{
IDBService dbService = new SQLService();
dbService.SetConfiguration(new Core.Entities.DBConfiguration()
{
ConnectionString = connectionString
});
var table = new TableData()
{
TableName = "dbo.Contacts"
};
var tablerow = new TableRowData();
tablerow.Add("FirstName", "Mario");
tablerow.Add("LastName", "Rossi");
tablerow.Add("Email", "*****@*****.**");
tablerow.Add("Height", 200);
tablerow.Add("BirthDate", new DateTime(1980, 2, 26));
table.Rows.Add(tablerow);
tablerow = new TableRowData();
tablerow.Add("FirstName", "Luigi");
tablerow.Add("LastName", "Bianchi");
tablerow.Add("Email", "*****@*****.**");
tablerow.Add("Height", 180);
tablerow.Add("BirthDate", new DateTime(1981, 2, 26));
table.Rows.Add(tablerow);
dbService.InsertTableDataAsync(table, default(CancellationToken)).GetAwaiter().GetResult();
}
public static string GetInsertStatement(string tableName, TableRowData row)
{
// INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES (N'Square Yards', N'Y2', GETDATE());
var strBuilder = new StringBuilder();
strBuilder.Append($"INSERT INTO {tableName} (");
for (int i = 0; i < row.Count; i++)
{
strBuilder.Append($"{row.Keys.ElementAt(i)}");
if (i < row.Count - 1)
{
strBuilder.Append(", ");
}
}
strBuilder.Append(") VALUES (");
for (int i = 0; i < row.Count; i++)
{
strBuilder.Append($"{GetSqlFormattedValue(row.Values.ElementAt(i))}");
if (i < row.Count - 1)
{
strBuilder.Append(", ");
}
}
strBuilder.Append(") ");
return(strBuilder.ToString());
}
private static void TestGetInsertStatementTable()
{
var table = new TableData()
{
TableName = "dbo.Contacts"
};
var tablerow = new TableRowData();
tablerow.Add("FirstName", "Mario");
tablerow.Add("LastName", "Rossi");
tablerow.Add("Email", "*****@*****.**");
tablerow.Add("Height", 200);
tablerow.Add("BirthDate", new DateTime(1980, 2, 26));
table.Rows.Add(tablerow);
tablerow = new TableRowData();
tablerow.Add("FirstName", "Luigi");
tablerow.Add("LastName", "Bianchi");
tablerow.Add("Email", "*****@*****.**");
tablerow.Add("Height", 180);
tablerow.Add("BirthDate", new DateTime(1981, 2, 26));
table.Rows.Add(tablerow);
var statement = QueryFactory.GetInsertStatement(table);
}
public string Render()
{
var table = string.Empty;
table += new TableRowHeader <TRowModel>(this).RenderRow();
for (var i = 0; i < ColumnImplementors.Count; i++)
{
table += new TableRowData <TRowModel>(this, i).RenderRow();
}
return(table);
}
private static void TestGetInsertStatement()
{
var tablerow = new TableRowData();
tablerow.Add("FirstName", "Massimo");
tablerow.Add("LastName", "Bonanni");
tablerow.Add("Email", "*****@*****.**");
tablerow.Add("Height", 175);
tablerow.Add("BirthDate", new DateTime(1970, 2, 26));
var statement = QueryFactory.GetInsertStatement("dbo.Contacts", tablerow);
}
public static TableRowData ToRowData(this ContactDTO contact)
{
if (contact == null)
{
throw new NullReferenceException(nameof(contact));
}
var row = new TableRowData();
row.Add(nameof(contact.FirstName), contact.FirstName);
row.Add(nameof(contact.LastName), contact.LastName);
row.Add(nameof(contact.Email), contact.Email);
row.Add(nameof(contact.BirthDate), contact.BirthDate);
row.Add(nameof(contact.Height), contact.Height);
return(row);
}
private async Task <TableData> GetChangesAsync(string tableName, string keyName, long trackingVersion, CancellationToken cancellationToken)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = QueryFactory.GetChangesQuery(tableName, keyName, trackingVersion);
connection.Open();
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
var columnNames = reader.GetColumnNames();
var tableData = new TableData()
{
TableName = tableName
};
while (await reader.ReadAsync(cancellationToken))
{
var fieldCount = reader.FieldCount;
var row = new object[fieldCount];
reader.GetValues(row);
var dataRow = new TableRowData();
for (var i = 0; i < row.Count() - 6; i++)
{
if (columnNames[i] == keyName) // retrieve key field from data change table to support delete operation
{
dataRow.Add(columnNames[i], row[row.Count() - 6]);
}
else
{
dataRow.Add(columnNames[i], row[i]);
}
}
dataRow.Operation = row[row.Count() - 1].ToRowOperation();
tableData.Rows.Add(dataRow);
}
return(tableData);
}
}
}
/// <summary>
/// 提交表单，返回行号
/// </summary>
/// <param name="tableId"></param>
/// <param name="form"></param>
/// <returns></returns>
public int SubmitRow(int tableId, NameValueCollection form)
{
//table name :cms_form_tableid
//column name:field_tableid_columnid
//button name:cms_form_tableid_btn
StringBuilder sb = new StringBuilder();
Regex cregex = new Regex("^field_\\d+_(\\d+)$");
IDictionary <int, string> rowsData = new Dictionary <int, string>();
foreach (string key in form)
{
if (cregex.IsMatch(key))
{
int columnId = int.Parse(cregex.Match(key).Groups[1].Value);
//sb.Append("$").Append(columnID.ToString()).Append("=").Append(form[key]);
rowsData.Add(columnId, form[key]);
}
}
TableRowData[] rows = new TableRowData[rowsData.Count];
int i = 0;
foreach (KeyValuePair <int, string> pair in rowsData)
{
rows[i] = new TableRowData
{
Value = pair.Value,
Cid = pair.Key
};
++i;
}
return(dal.CreateRow(tableId, rows));
}
