public DataSet GetDataSet(string dataBaseName, string procedureName, IdbParameter parameters = null)
{
DataSet ds = new DataSet("Table");
SqlDataAdapter da = new SqlDataAdapter();
try
{
using (SqlConnection conn = new SqlConnection(GenerateConnectionstring(dataBaseName)))
{
SqlCommand sqlComm = new SqlCommand(procedureName, conn);
sqlComm.Parameters.Clear();
if (parameters != null)
{
var _parameter = (parameters as DbParameter).Parameters;
sqlComm.Parameters.AddRange(_parameter.ToArray <SqlParameter>());
}
sqlComm.CommandType = CommandType.StoredProcedure;
da.SelectCommand = sqlComm;
da.Fill(ds);
}
return(ds);
}
finally
{
da.Dispose();
da = null;
ds = null;
}
}
public void ExecuteNonQuery(string dataBaseName, string procedureName, IdbParameter parameters = null)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
using (SqlConnection conn = new SqlConnection(GenerateConnectionstring(dataBaseName)))
{
SqlCommand sqlComm = new SqlCommand(procedureName, conn);
sqlComm.Parameters.Clear();
if (parameters != null)
{
var _parameter = (parameters as DbParameter).Parameters;
sqlComm.Parameters.AddRange(_parameter.ToArray <SqlParameter>());
}
sqlComm.CommandType = CommandType.StoredProcedure;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
sqlComm.ExecuteNonQuery();
}
}
finally
{
da.Dispose();
da = null;
}
}
public IdbParameter UpdateSqlParamByObj(object obj, string[] parameter, IdbParameter parameters)
{
if (parameter == null)
{
throw new Exception("Parameter value is null.");
}
if (obj == null)
{
throw new Exception("object value is null.");
}
if (parameters == null)
{
throw new Exception("parameter value is null.");
}
var _parameter = parameters as DbParameter;
Type _type = obj.GetType();
IList <PropertyInfo> props = new List <PropertyInfo>(_type.GetProperties(BindingFlags.Public | BindingFlags.Instance));
foreach (var prop in props)
{
object value = prop.GetValue(obj, null);
SqlParameter param = null;
if (parameter.Contains(prop.Name))
{
if (prop.GetValue(obj, null) != null)
{
Type type = prop.GetValue(obj, null).GetType();
if (_dataTypeList[0] == type || _dataTypeList[1] == type || _dataTypeList[2] == type)
{
param = new SqlParameter();
param.DbType = DbType.Int32;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = Convert.ToInt32(value);
_parameter.Parameters.Add(param);
}
else if (_dataTypeList[3] == type)
{
param = new SqlParameter();
param.DbType = DbType.String;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = Convert.ToString(value);
_parameter.Parameters.Add(param);
}
else if (_dataTypeList[4] == type)
{
param = new SqlParameter();
param.DbType = DbType.Double;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = Convert.ToDouble(value);
_parameter.Parameters.Add(param);
}
else if (_dataTypeList[5] == type)
{
param = new SqlParameter();
param.DbType = DbType.Boolean;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = Convert.ToBoolean(value);
_parameter.Parameters.Add(param);
}
else if (_dataTypeList[6] == type)
{
param = new SqlParameter();
param.DbType = DbType.DateTime;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = Convert.ToDateTime(value);
_parameter.Parameters.Add(param);
}
else if (_dataTypeList[7] == type)
{
param = new SqlParameter();
param.SqlDbType = SqlDbType.VarBinary;
param.ParameterName = "@" + prop.Name.Trim();
param.Value = (byte[])value;
_parameter.Parameters.Add(param);
}
}
}
}
return(_parameter);
}
public IdbParameter UpdateSqlParameterByDataTable(string name, DataTable value, string type, IdbParameter parameters)
{
if (parameters == null)
{
throw new Exception("parameter value is null.");
}
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + name.Trim();
param.SqlDbType = SqlDbType.Structured;
param.TypeName = type;
param.Value = value;
(parameters as DbParameter).Parameters.Add(param);
return(parameters);
}
public IdbParameter UpdateSqlParameter(string name, object value, DataType type, IdbParameter parameters)
{
if (parameters == null)
{
throw new Exception("parameter value is null.");
}
var param = new SqlParameter();
switch (type)
{
case DataType.AsInt:
param.DbType = DbType.Int32;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToInt32(value);
break;
case DataType.AsString:
param.DbType = DbType.String;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToString(value);
break;
case DataType.AsBoolean:
param.DbType = DbType.Boolean;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToBoolean(value);
break;
case DataType.AsDataTime:
param.DbType = DbType.DateTime;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToDateTime(value);
break;
case DataType.AsDouble:
param.DbType = DbType.Double;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToDateTime(value);
break;
case DataType.AsByteArray:
param.SqlDbType = SqlDbType.VarBinary;
param.ParameterName = "@" + name.Trim();
param.Value = (byte[])value;
break;
default:
param.DbType = DbType.String;
param.ParameterName = "@" + name.Trim();
param.Value = Convert.ToString(value);
break;
}
(parameters as DbParameter).Parameters.Add(param);
return(parameters);
}
public List <T> GetDataTableToList <T>(string dataBaseName, string procedureName, IdbParameter parameters = null)
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
List <T> dataResult = null;
try
{
using (SqlConnection conn = new SqlConnection(GenerateConnectionstring(dataBaseName)))
{
SqlCommand sqlComm = new SqlCommand(procedureName, conn);
sqlComm.Parameters.Clear();
if (parameters != null)
{
var _parameter = (parameters as DbParameter).Parameters;
sqlComm.Parameters.AddRange(_parameter.ToArray <SqlParameter>());
}
sqlComm.CommandType = CommandType.StoredProcedure;
da.SelectCommand = sqlComm;
da.Fill(dt);
}
var stringDT = JsonConvert.SerializeObject(dt);
dataResult = JsonConvert.DeserializeObject <List <T> >(stringDT, new IntegerConverter());
return(dataResult);
}
finally
{
da.Dispose();
dataResult = null;
da = null;
}
}