public void DeleteRow(string connectionString, string tableName)
{
var db = new StoreProcdureManagement();
var sqlCommandString = "DELETE FROM " + tableName;
var whereCondition = "";
lock (_filters)
{
var counter = 0;
foreach (var f in _filters)
{
if (whereCondition != "")
{
whereCondition += " AND ";
}
var param = "@param" + counter;
counter++;
whereCondition += f.ColumnName + " " + GetOprand(f.WhereFilter) + " " + param;
db.AddParameter(param, f.Value);
}
_filters.Clear();
}
if (whereCondition != "")
{
whereCondition = " WHERE " + whereCondition;
}
sqlCommandString += whereCondition;
db.RunQuery(connectionString, sqlCommandString);
}
public List <T> GetTable <T>(string connectionString, string tableName = "fromT")
{
var db = new StoreProcdureManagement();
var _tableName = tableName;
if (tableName == "fromT")
{
_tableName = typeof(T).Name;
}
var param = "";
var counter = 0;
var whereCondition = "";
lock (_filters)
{
foreach (var f in _filters)
{
if (whereCondition != "")
{
whereCondition += " AND ";
}
param = "@param" + counter;
counter++;
whereCondition += f.ColumnName + " " + GetOprand(f.WhereFilter) + " " + param;
db.AddParameter(param, f.Value);
}
_filters.Clear();
}
if (whereCondition != "")
{
whereCondition = " WHERE " + whereCondition;
}
var columns = "";
lock (_customColumn)
{
foreach (var col in _customColumn)
{
if (columns != "")
{
columns += ", ";
}
columns += " " + col.Key + " " + col.Value;
}
_customColumn.Clear();
}
return(db.RunQuery <T>(connectionString,
"SELECT " + (columns == "" ? "*" : columns)
+ " FROM " + _tableName + whereCondition));
}
private List <string> GetPrimaryKeys(string connectionString, string tableName)
{
var db = new StoreProcdureManagement();
string command = "SELECT " +
" COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName" +
" FROM sys.indexes AS i INNER JOIN" +
" sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID" +
" AND i.index_id = ic.index_id" +
" WHERE i.is_primary_key = 1 AND OBJECT_NAME(ic.OBJECT_ID) = '" + tableName + "'";
return(db.RunQuery <string>(connectionString, command));
}
public void UpdateTable(string connectionString, object obj, string tableName = "fromObjectName",
Dictionary <string, string> replacements = null,
System.Collections.Generic.List <string> ignoreList = null)
{
var db = new StoreProcdureManagement();
if (tableName == "fromObjectName")
{
tableName = obj.GetType().Name;
}
var sqlCommandString = "Select TOP 1 * FROM " + tableName;
var dataset = db.RunQuery(connectionString, sqlCommandString);
var table = dataset.Tables[0];
var dic = obj.ToDictionary();
if (replacements != null)
{
foreach (var newName in replacements)
{
if (dic.Keys.Contains(newName.Key))
{
dic.Add(newName.Value, dic[newName.Key]);
dic.Remove(newName.Key);
}
}
}
sqlCommandString = "UPDATE " + tableName + " SET ";
var fields = "";
//var values = "";
var valueList = new List <string>();
var counter = 0;
//var sqlParameter = new List<SqlParameter>();
var primaryKeys = GetPrimaryKeys(connectionString, tableName);
var param = "";
foreach (var record in dic)
{
if ((ignoreList == null || !ignoreList.Contains(record.Key)) && primaryKeys.All(r => r != record.Key))
{
if (!table.Columns.Contains(record.Key))
{
throw new Exception("فیلد '" + record.Key + "' در جدول وجود ندارد");
}
if (fields != "")
{
fields += ", ";
//values += ",";
}
param = "@param" + counter;
fields += record.Key + " = " + param;
db.AddParameter(param, record.Value);
counter++;
}
}
sqlCommandString += fields;
var whereCondition = "";
lock (_filters)
{
foreach (var f in _filters)
{
if (whereCondition != "")
{
whereCondition += " AND ";
}
param = "@param" + counter;
counter++;
whereCondition += f.ColumnName + " " + GetOprand(f.WhereFilter) + " " + param;
db.AddParameter(param, f.Value);
}
_filters.Clear();
}
if (whereCondition != "")
{
whereCondition = " WHERE " + whereCondition;
}
sqlCommandString += whereCondition;
db.RunQuery(connectionString, sqlCommandString);
}
public void InsertToTable(string connectionString, object obj, string tableName = "fromObjectName",
Dictionary <string, string> replacements = null,
System.Collections.Generic.List <string> ignoreList = null)
{
var db = new StoreProcdureManagement();
if (tableName == "fromObjectName")
{
tableName = obj.GetType().Name;
}
var sqlCommandString = "Select TOP 1 * FROM " + tableName;
var dataset = db.RunQuery(connectionString, sqlCommandString);
var table = dataset.Tables[0];
var dic = obj.ToDictionary();
if (replacements != null)
{
foreach (var newName in replacements)
{
if (dic.Keys.Contains(newName.Key))
{
dic.Add(newName.Value, dic[newName.Key]);
dic.Remove(newName.Key);
}
}
}
sqlCommandString = "INSERT INTO " + tableName;
var fields = "";
var values = "";
var valueList = new List <string>();
var counter = 0;
var sqlParameter = new List <SqlParameter>();
foreach (var record in dic)
{
if (ignoreList == null || !ignoreList.Contains(record.Key))
{
if (!table.Columns.Contains(record.Key))
{
throw new Exception("فیلد '" + record.Key + "' در جدول وجود ندارد");
}
if (fields != "")
{
fields += ", ";
values += ",";
}
fields += record.Key;
var param = "@param" + counter;
values += param;
db.AddParameter(param, record.Value);
counter++;
}
}
sqlCommandString += " (" + fields + ") VALUES(" + values + ")";
db.RunQuery(connectionString, sqlCommandString);
}
public DataSet RunSp(string spName, string connectionString, Dictionary <string, string> parameters = null)
{
var getUserDefinedColumnsQuery = @"select c.Name from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
where tt.name = 'Parameters' order by c.column_id";
var tableList = new StoreProcdureManagement().RunQuery(connectionString, getUserDefinedColumnsQuery).Tables;
if (tableList.Count < 1)
{
return(new DataSet());
}
var paramTable = new DataTable();
//paramTable.Columns.Add("Type", typeof(string));
//paramTable.Columns.Add("Name", typeof(string));
//paramTable.Columns.Add("Value", typeof(string));
for (var i = 0; i < tableList[0].Rows.Count; i++)
{
paramTable.Columns.Add(tableList[0].Rows[i]["Name"].ToString(), typeof(string));
}
//paramTable.Columns.Add("Type", typeof(string));
//paramTable.Columns.Add("Value", typeof(string));
var connection = new SqlConnection {
ConnectionString = connectionString
};
var command = new SqlCommand(_runSpFullName, connection)
{
CommandType = CommandType.StoredProcedure
};
command.CommandTimeout = 3600;
var row = paramTable.NewRow();
row["Name"] = "_SpName";
row["Value"] = spName;
row["Type"] = "";
paramTable.Rows.Add(row);
if (parameters != null)
{
foreach (var parameter in parameters)
{
row = paramTable.NewRow();
row["Name"] = parameter.Key;
row["Type"] = "";
row["Value"] = parameter.Value;
paramTable.Rows.Add(row);
}
}
command.Parameters.AddWithValue("@params", paramTable);
//var param = new SqlParameter("@params", paramTable);
//command.Parameters.Add(param);
//command.Parameters.Add("@params", SqlDbType.Structured);
//command.Parameters["@params"].Value = paramTable;
try
{
var adapter = new SqlDataAdapter(command);
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables.Count > 1)
{
for (var i = 0; i < dataSet.Tables.Count - 1; i++)
{
var tableName = "";
if (dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"] != null && dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"].ToString() != "")
{
tableName = dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"].ToString();
}
else
{
switch (dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["TableType"].ToString())
{
case "M":
tableName = "Message";
break;
case "S":
tableName = "Schema";
break;
case "E":
var sqlSpException = new DataException();
sqlSpException.Add(dataSet.Tables[i]);
throw sqlSpException;
}
}
dataSet.Tables[i].TableName = tableName;
}
}
dataSet.Tables[dataSet.Tables.Count - 1].TableName = "TableDefination";
return(dataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}