Last active
January 3, 2020 02:29
-
-
Save tvolodimir/6550256 to your computer and use it in GitHub Desktop.
Generic sql procedure executor C#
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class GenericSqlProvider | |
{ | |
private readonly string connectionString; | |
public GenericSqlProvider(string connectionString) | |
{ | |
this.connectionString = connectionString; | |
} | |
public IResult<ProcedureResult> ExecuteCall(string procedureName, IDictionary<string, object> input) | |
{ | |
using (var connection = new SqlConnection(connectionString)) | |
{ | |
var command = new SqlCommand(procedureName, connection) { CommandType = CommandType.StoredProcedure }; | |
var parameters = input.Select(p => | |
{ | |
var param = command.CreateParameter(); | |
param.ParameterName = p.Key; | |
param.Value = p.Value; | |
return param; | |
}).ToArray(); | |
SqlParameter sampParm = command.Parameters.Add("RETURN_VALUE", SqlDbType.Int); | |
sampParm.Direction = ParameterDirection.ReturnValue; | |
command.Parameters.AddRange(parameters); | |
try | |
{ | |
connection.Open(); | |
var reader = command.ExecuteReader(); | |
/*var tables = new List<DataTable>(); | |
while (!reader.IsClosed) | |
{ | |
DataTable schemaTable = reader.GetSchemaTable(); | |
foreach (DataRow row in schemaTable.Rows) | |
{ | |
foreach (DataColumn column in schemaTable.Columns) | |
{ | |
Console.WriteLine("{0} = {1}", column.ColumnName, row[column]); | |
} | |
} | |
Console.WriteLine("\n"); | |
var table = new DataTable(); | |
table.Load(reader); | |
tables.Add(table); | |
foreach (DataRow row in table.Rows) | |
{ | |
foreach (DataColumn column in table.Columns) | |
{ | |
Console.WriteLine("{0} = {1}", column.ColumnName, row[column]); | |
} | |
} | |
Console.WriteLine("\n"); | |
}*/ | |
var results = new List<ICollection<DataRowObject>>(); | |
if (reader.HasRows) | |
{ | |
var dataObj = new List<DataRowObject>(); | |
while (reader.Read()) | |
{ | |
var dataRow = new DataRowObject(); | |
for (int i = 0; i < reader.FieldCount; i++) | |
{ | |
dataRow.Add(reader.GetName(i), reader[i]); | |
} | |
dataObj.Add(dataRow); | |
} | |
results.Add(dataObj); | |
} | |
else | |
{ | |
results.Add(null); | |
} | |
while (reader.NextResult()) | |
{ | |
if (reader.HasRows) | |
{ | |
var dataObj = new List<DataRowObject>(); | |
while (reader.Read()) | |
{ | |
var dataRow = new DataRowObject(); | |
for (int i = 0; i < reader.FieldCount; i++) | |
{ | |
dataRow.Add(reader.GetName(i), reader[i]); | |
} | |
dataObj.Add(dataRow); | |
} | |
results.Add(dataObj); | |
} | |
else | |
{ | |
results.Add(null); | |
} | |
} | |
var returnValue = 0; | |
try | |
{ | |
returnValue = (int)command.Parameters["RETURN_VALUE"].Value; | |
} | |
// ReSharper disable EmptyGeneralCatchClause | |
catch { } | |
// ReSharper restore EmptyGeneralCatchClause | |
return new Result<ProcedureResult>(new ProcedureResult(results.Count == 1 && results[0] == null ? null : results, returnValue)); | |
} | |
catch (Exception ex) | |
{ | |
return new Result<ProcedureResult>(null, (int)ExceptionType.DB_TIMEOUT, ex.Message); | |
} | |
finally | |
{ | |
connection.Close(); | |
} | |
} | |
} | |
} | |
public class ProcedureResult | |
{ | |
public IEnumerable<IEnumerable<DataRowObject>> Results { get; private set; } | |
public int ReturnValue { get; private set; } | |
public ProcedureResult(IEnumerable<IEnumerable<DataRowObject>> results, int returnValue = 0) | |
{ | |
Results = results == null ? null : results.Cast<List<DataRowObject>>().ToList(); | |
ReturnValue = returnValue; | |
} | |
} | |
public class DataRowObject : NameObjectCollectionBase | |
{ | |
public void Add(string key, object value) | |
{ | |
BaseAdd(key, value); | |
} | |
public KeyValuePair<string, object> this[int index] | |
{ | |
get { return new KeyValuePair<string, object>(BaseGetKey(index), BaseGet(index)); } | |
} | |
public object this[string key] { get { return BaseGet(key); } } | |
public override System.Collections.IEnumerator GetEnumerator() | |
{ | |
for (int i = 0; i < Count; i++) | |
{ | |
yield return this[i]; | |
} | |
} | |
} | |
public interface IResult | |
{ | |
bool Success { get; } | |
string ErrorMessage { get; } | |
int ErrorCode { get; } | |
} | |
public class Result : IResult | |
{ | |
public bool Success { get; private set; } | |
public string ErrorMessage { get; private set; } | |
public int ErrorCode { get; private set; } | |
public Result(int errorCode = 0, string errorMessage = "") | |
{ | |
ErrorCode = errorCode; | |
ErrorMessage = errorMessage; | |
Success = (errorCode == 0) && string.IsNullOrEmpty(errorMessage); | |
} | |
public static Result Successful = new Result(); | |
public override string ToString() | |
{ | |
return Success ? "Success" : string.Format("{0}({1})", ErrorCode, ErrorMessage); | |
} | |
} | |
public interface IResult<out T> : IResult | |
{ | |
T Data { get; } | |
} | |
public class Result<TResult> : Result, IResult<TResult> | |
{ | |
public TResult Data { get; private set; } | |
public Result(TResult data, int errorCode = 0, string errorMessage = "") | |
: base(errorCode, errorMessage) | |
{ | |
Data = data; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment