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