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)); }