public DataTable GetDate()
{
string ReadQuery = $@"select {string.Join(",", Fileds.Select(x => x.InternalName))} from {TableName}";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(ReadQuery, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable Result = new DataTable();
da.Fill(Result);
conn.Close();
da.Dispose();
return(Result);
}
public int Insert(DataTable _dt, List <ColumnAssignment> ColumnAssignments)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand($@"insert into {TableName} ({string.Join(",", ColumnAssignments.Select(x => x.ToColumn.InternalName))}) values
(@{string.Join(",@", ColumnAssignments.Select(x => x.ToColumn.InternalName))})", conn);
conn.Open();
int Result = 0;
foreach (DataRow r in _dt.Rows)
{
foreach (var ColumnAssignment in ColumnAssignments)
{
object value = r[ColumnAssignment.FromColumn.InternalName];
IColumn column = Fileds.Where(x => x.InternalName == ColumnAssignment.ToColumn.InternalName).FirstOrDefault();
if (column.WriteDataPipeline != null)
{
value = column.WriteDataPipeline.ApplayOperator(value);
}
if (!cmd.Parameters.Contains("@" + ColumnAssignment.ToColumn.InternalName))
{
cmd.Parameters.Add("@" + ColumnAssignment.ToColumn.InternalName, SqlDbType.Text);
}
cmd.Parameters["@" + ColumnAssignment.ToColumn.InternalName].Value = value;
}
Result += cmd.ExecuteNonQuery();
}
conn.Close();
sanitisation?.Apply();
return(Result);
}
