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