public void Execute(ConnectionParameters Parameters, string sSqlCommand) { string sSqlConnection; sSqlConnection = "Data Source=" + Parameters.ServerName + ",1433;Initial Catalog=" + Parameters.DataBaseName + ";User ID=" + Parameters.User + ";Password="******""; try { using (SqlConnection connectionSql = new SqlConnection(sSqlConnection)) { SqlCommand commandSql = new SqlCommand(sSqlCommand, connectionSql); connectionSql.Open(); commandSql.ExecuteNonQuery(); connectionSql.Close(); commandSql = null; } } catch (Exception) { throw; } }
public string[] Get(ConnectionParameters Parameters, string sSqlCommand, string[] rowSelector, string[] columnSelector, bool HeaderToFirstRow = false) { string sSqlConnection; // DataTable dtSource; StringBuilder sbRetVal = new StringBuilder(); StringBuilder sbHeader = new StringBuilder(); StringBuilder sbRetDataDef = new StringBuilder(); string[] result = new string[2]; sSqlConnection = "Data Source=" + Parameters.ServerName + ";Initial Catalog=" + Parameters.DataBaseName + ";User ID=" + Parameters.User + ";Password="******""; try { using (SqlConnection connectionSql = new SqlConnection(sSqlConnection)) { connectionSql.Open(); SqlCommand commandSql = new SqlCommand(sSqlCommand, connectionSql); // create data adapter SqlDataAdapter da = new SqlDataAdapter(commandSql); // this will query your database and return the result to your datatable //SqlDataReader sdr = commandSql.ExecuteReader(); DataTable dtSource = new DataTable(); // dtSource.Load(sdr); da.Fill(dtSource); connectionSql.Close(); commandSql = null; da.Dispose(); foreach (DataColumn column in dtSource.Columns) { sbRetDataDef.Append(column.ColumnName); sbRetDataDef.Append(":"); sbRetDataDef.Append(column.DataType); sbRetDataDef.Append(";"); if (HeaderToFirstRow) { sbHeader.Append(column.ColumnName); sbHeader.Append(columnSelector[0]); } } DataTableReader reader = new DataTableReader(dtSource); sbHeader.Append(rowSelector[0]); if (HeaderToFirstRow) { sbRetVal.Append(sbHeader.ToString()); } while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { string value; if (reader[i].GetType() == typeof(System.Decimal)) { value = ((decimal)reader[i]).ToString("0.000",CultureInfo.InvariantCulture); } else if (reader[i].GetType() == typeof(System.DateTime)) { value = ((DateTime)reader[i]).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture); } else { value = reader[i].ToString(); } sbRetVal.Append(!String.IsNullOrEmpty(reader[i].ToString()) ? value : "NULL"); sbRetVal.Append(columnSelector[0]); } sbRetVal.Append(rowSelector[0]); } } } catch (SqlException) { throw; } catch (Exception) { throw; } finally { } result[0] = sbRetDataDef.ToString(); result[1]= sbRetVal.ToString(); return result; }
public void Insert(ConnectionParameters Parameters, string sDestinationTable, string Data, List<TableDefinition> TableDefintion,string[] rowSelector, string[] columnSelector) { string sSqlCommand = string.Empty; string sSqlConnection; string[] rows; string[] columns; StringBuilder columnvalues = new StringBuilder(); StringBuilder columnnames = new StringBuilder(); StringBuilder defaultcolumnnames = new StringBuilder(); StringBuilder defaultcolumnvalues = new StringBuilder(); sSqlConnection = "Data Source=" + Parameters.ServerName + ";Initial Catalog=" + Parameters.DataBaseName + ";User ID=" + Parameters.User + ";Password="******""; rows = Data.Split(rowSelector,StringSplitOptions.RemoveEmptyEntries); try { using (SqlConnection connectionSql = new SqlConnection(sSqlConnection)) { foreach (var item in TableDefintion) { if (String.IsNullOrEmpty(item.DefaultValue)) { columnnames.Append("[" + item.FieldName + "],"); } else { defaultcolumnnames.Append("[" + item.FieldName + "],"); defaultcolumnvalues.Append(",'" + item.DefaultValue + "'" ); } } columnnames.Append(defaultcolumnnames.ToString()); columnnames.Remove(columnnames.Length - 1, 1); connectionSql.Open(); foreach (string row in rows) { columns = row.Split(columnSelector, StringSplitOptions.RemoveEmptyEntries); int columnCount=1; bool isValid = true; foreach (var column in columns) { var tableDef = TableDefintion.FirstOrDefault(f => f.FieldOrder == columnCount); var Datatype = tableDef.DataType; if (tableDef.IsNotNullable == true && column == "NULL") { isValid = false; break; } switch (Datatype) { case "Date": columnvalues.Append(column == "NULL" ? column : "convert(date,CONVERT(datetime,convert(int,'" + column.Replace("'", "'+ CHAR(39)+'") + "')))"); break; default: columnvalues.Append(column == "NULL" ? column : "'" + column.Replace("'", "'+ CHAR(39)+'") + "'"); break; } columnvalues.Append(","); columnCount++; } if (isValid) { columnvalues.Remove(columnvalues.Length - 1, 1); sSqlCommand = "insert into " + sDestinationTable + "(" + columnnames.ToString() + ") Values (" + columnvalues.ToString() + defaultcolumnvalues.ToString() + ")"; SqlCommand commandSql = new SqlCommand(sSqlCommand.ToString(), connectionSql); commandSql.ExecuteNonQuery(); commandSql = null; } columnvalues.Clear(); } connectionSql.Close(); } } catch (Exception ex) { throw; } }