public static System.Data.DataTable mClient_FilterClient(string TenCardClient)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Client_FiltClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PCodCardClient;
Parameter.Value = TenCardClient;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
public static DbCommand CreateCommand(string sqlStringCommand, DbParameterCollection dbParameters)
{
string dataProviderName = UniversityChatConfiguration.DbProviderName;
string connectionString = UniversityChatConfiguration.DbConnectionString;
DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
DbCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.Text;
//Create the dbParameter:
if(dbParameters != null)
{
foreach(DbParameter param in dbParameters)
{
DbParameter dbParameter = comm.CreateParameter();
dbParameter.ParameterName = param.ParameterName;
dbParameter.Value = param.Value;
dbParameter.DbType = param.DbType;
comm.Parameters.Add(dbParameter);
}
}
//Set the command text:
comm.CommandText = sqlStringCommand;
return comm;
}
private static void AddParameters(Query query, DbParameterCollection parameters) {
foreach (DbParameter parameter in parameters) {
if (parameter.Direction == ParameterDirection.Input) {
query.Parameters[parameter.ParameterName] = parameter.Value;
}
}
}
public static System.Data.DataTable mCashs_OneShift(Guid KeyTienGiao)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_OneShift;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyTienGiao;
Parameter.Value = KeyTienGiao;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
/// <summary>
/// Gets the table storage request for the given settings and values
/// </summary>
public TableStorageRequest GetTableStorageRequest(TableStorageSettings settings, DbParameterCollection parameters)
{
var rowKey = string.Empty;
var partitionKey = string.Empty;
var requestParameters = new List<string>();
var bodyParameters = new List<string>();
for (var i = setIndex + 1; i < whereIndex; i += 3)
{
bodyParameters.Add(commandTextParts[i]);
}
var rowKeyIndex = Array.FindIndex(commandTextParts, s => s == "RowKey");
var rowKeyValueIndex = commandTextParts[rowKeyIndex + 2];
var rowKeyValue = rowKeyValueIndex == "null" ? null : parameters[int.Parse(rowKeyValueIndex.TrimStart('p'))].Value.ToString();
requestParameters.Add("RowKey=" + "'" + rowKeyValue + "'");
rowKey = rowKeyValue;
var partitionKeyIndex = Array.FindIndex(commandTextParts, s => s == "PartitionKey");
var partitionKeyValueIndex = commandTextParts[partitionKeyIndex + 2];
var partitionKeyValue = partitionKeyValueIndex == "null" ? null : parameters[int.Parse(partitionKeyValueIndex.TrimStart('p'))].Value.ToString();
requestParameters.Add("PartitionKey=" + "'" + partitionKeyValue + "'");
partitionKey = partitionKeyValue;
var uri = String.Format(settings.Uri.AbsoluteUri + "{0}({1})", tableName, String.Join(",", requestParameters.ToArray()));
var request = new TableStorageRequest(StorageHttpConstants.HttpMethod.Merge, uri, settings) { IfMatch = Tracker.GetIfMatchHeaderFor(tableName, rowKey, partitionKey) };
for (var i = 0; i < bodyParameters.Count; i++)
{
request.Body.AddProperty(bodyParameters[i], parameters[i].Value, parameters[i].DbType);
}
return request;
}
public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
{
foreach (DbParameter dbParameter in dbParameterCollection)
{
cmd.Parameters.Add(dbParameter);
}
}
/// <summary>
/// Lay danh sach don hang thuoc cua khach hang
/// </summary>
/// Tra ve: Danh sach don hang duoc dang ky
public static System.Data.DataTable m2018_GetallOrders(Guid KeyCardClient)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_GetallOrders;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyCardClient;
Parameter.Value = KeyCardClient;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch (Exception ex) {
DataTable = null;
}
return(DataTable);
}
}
public static System.Data.DataTable mCashs_SumBrand(DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_SumBrand;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayBatDau;
Parameter.Value = NgayBatDau;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayKetThuc;
Parameter.Value = NgayKetThuc;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
private static IEnumerable<object> ExtractValuesFrom(DbParameterCollection parameters)
{
foreach (DbParameter parameter in parameters)
{
if (parameter.DbType == DbType.Boolean)
{
yield return parameter.Value.ToString().ToLower();
}
else if (parameter.DbType == DbType.Int16
|| parameter.DbType == DbType.Int32)
{
yield return parameter.Value;
}
else if (parameter.DbType == DbType.Int64)
{
yield return parameter.Value + "L";
}
else if (parameter.DbType == DbType.Guid)
{
yield return "guid'" + parameter.Value + "'";
}
else if (parameter.DbType == DbType.DateTime)
{
yield return "datetime'" + parameter.Value + "'";
}
else
{
yield return "'" + parameter.Value + "'";
}
}
}
public static DataTable mSales_OneStoreChart(Guid KeyChiNhanh, DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.CreateCommand())
{
DbCommand.CommandText = SpMobile_Sales_OneCharts;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PKeyChiNhanh;
DbParameter.Value = KeyChiNhanh;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayBatDau;
DbParameter.Value = NgayBatDau;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayKetThuc;
DbParameter.Value = NgayKetThuc;
DbParameters.Add(DbParameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch (Exception ex) { DataTable = null; }
return(DataTable);
}
}
public static System.Data.DataTable mGoods_FilterGoods(decimal TileHangDuoi, decimal TileHangTren)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Goods_FiltLists;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PTileHangDuoi;
Parameter.Value = TileHangDuoi;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PTileHangTren;
Parameter.Value = TileHangTren;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
public static DbParameterCollection SetValue(this System.Data.Common.DbParameterCollection collection, string paramName,
object value)
{
collection[paramName].Value = value;
return(collection);
}
internal StubDataMapper(DbProviderFactory dbfactory, DbCommand command, DbConnection connection, DbParameterCollection parameters)
: base(dbfactory, "connection string")
{
_command = command;
_connection = connection;
_parameters = parameters;
}
public OrganizationRequestBuilderVisitor(ICrmMetaDataProvider crmMetadataProvider, DbParameterCollection parameters, IDynamicsAttributeTypeProvider typeProvider, ConnectionSettings settings = null)
{
CrmMetadataProvider = crmMetadataProvider;
Parameters = parameters;
TypeProvider = typeProvider;
Settings = settings ?? ConnectionSettings.Default();
// OrgCommand = new OrganisationRequestCommand();
}
private string GetRequestUri(string baseUri, DbParameterCollection parameters)
{
var requestUri = String.Format(baseUri + "{0}()", tableName);
if (filter != null)
{
var values = ExtractValuesFrom(parameters);
requestUri += String.Format(filter, values.ToArray());
}
return requestUri;
}
private static void ProvideMissingParameters(System.Data.Common.DbParameterCollection parameters)
{
SqlParameter userId = new SqlParameter("@UserId", System.Data.SqlDbType.Int);
SqlParameter price = new SqlParameter("@Price", System.Data.SqlDbType.Money);
SqlParameter contactInfo = new SqlParameter("@ContactInfo", System.Data.SqlDbType.NText);
userId.Value = DBNull.Value;
price.Value = DBNull.Value;
contactInfo.Value = DBNull.Value;
parameters.AddRange(new SqlParameter[] { userId, price, contactInfo });
}
/// <summary>
/// Initializes a new instance of the <see cref="DbQueryInfo"/> class.
/// </summary>
internal DbQueryInfo(ICollection<EntitySetBase> affectedEntitySets, string commandText,
DbParameterCollection parameters)
{
Debug.Assert(affectedEntitySets != null);
Debug.Assert(commandText != null);
Debug.Assert(parameters != null);
_affectedEntitySets = affectedEntitySets;
_commandText = commandText;
_parameters = parameters;
}
/// <summary>
/// Gets the table storage request for the given settings and values
/// </summary>
public TableStorageRequest GetTableStorageRequest(TableStorageSettings settings, DbParameterCollection parameters)
{
var parameterNames = new string[parameters.Count];
Array.Copy(commandTextParts, 3, parameterNames, 0, parameters.Count);
var request = new TableStorageRequest(StorageHttpConstants.HttpMethod.Post, String.Format(settings.Uri.AbsoluteUri + "{0}", tableName), settings);
for (var i = 0; i < parameterNames.Length; i++)
{
request.Body.AddProperty(parameterNames[i], parameters[i].Value, parameters[i].DbType);
}
return request;
}
public static decimal mCashs_SumSales(Guid KeyChiNhanh, Guid KeyThuNgan, Guid KeyNguoiDung,
DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_SumSales;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyChiNhanh;
Parameter.Value = KeyChiNhanh;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyThuNgan;
Parameter.Value = KeyThuNgan;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyNguoiDung;
Parameter.Value = KeyNguoiDung;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayBatDau;
Parameter.Value = NgayBatDau;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayKetThuc;
Parameter.Value = NgayKetThuc;
Parameters.Add(Parameter);
decimal nSumBan = 0;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{
if (DbDataReader.Read())
{
nSumBan = DbDataReader.GetDecimal(DbDataReader.GetOrdinal(PBanTienGiao));
DbDataReader.Close();
}
}
}
catch { nSumBan = 0; }
return(nSumBan);
}
}
private static void GetParameterOut(LBDbParameterCollection tsParms, System.Data.Common.DbParameterCollection dbParms)
{
if (tsParms != null && tsParms.Count > 0)
{
foreach (LBDbParameter parm in tsParms)
{
if (parm.Direction != ParameterDirection.Input)
{
parm.Value = dbParms["@" + parm.ParameterName].Value;
}
}
}
}
public static string GetQueryDetail(string commandText, DateTime dtStart, DateTime dtEnd, DbParameterCollection cmdParams)
{
string tr = "<tr>";
string colums = "";
string dbtypes = "";
string values = "";
string paramdetails = "";
if (cmdParams != null && cmdParams.Count > 0)
{
foreach (DbParameter param in cmdParams)
{
if (param == null)
{
continue;
}
colums += "<td style=\"text-align:center\">" + param.ParameterName + "</td>";
dbtypes += "<td style=\"text-align:center\">" + param.DbType.ToString() + "</td>";
values += "<td style=\"text-align:center\">" + param.Value.ToString() + "</td>";
}
paramdetails = string.Format("<table cellspacing=\"5px\" cellpadding=\"5px\" style=\"font-size: 12px;\">{0}{1}</tr>{0}{2}</tr>{0}{3}</tr></table>", tr, colums, dbtypes, values);
}
string outputstring = string.Format("<div style=\"border: 1px solid black; padding: 5px; width: 98%; clear: both; margin: 5px auto; text-align: left\"><div style=\"font-size: 12px; padding: 5px;\"><b>耗时:</b> {0}</div><div style=\"font-size: 12px; line-height: 18px; padding: 5px;\">{1}</div>{2}</div><br/>", dtEnd.Subtract(dtStart).ToString(), commandText, paramdetails);
/*
//写入文件操作
string debugsql = "";
debugsql += "\r\nTIME:" + dtEnd.Subtract(dtStart).ToString();
debugsql += "\r\nSQL:" + commandText;
if (cmdParams != null && cmdParams.Count > 0)
{
foreach (DbParameter param in cmdParams)
{
if (param == null)
{
continue;
}
debugsql += "\r\nParameterName:" + param.ParameterName;
debugsql += " DbType:" + param.DbType.ToString();
debugsql += " Value:" + param.Value.ToString();
}
}
//WriteDdbguSQLFile(debugsql);
*/
return outputstring;
}
/// <summary>
/// Gets the table storage request for the given settings and values
/// </summary>
public TableStorageRequest GetTableStorageRequest(TableStorageSettings settings, DbParameterCollection parameters)
{
var requestParameters = new List<string>();
var rowKeyIndex = Array.FindIndex(commandTextParts, s => s == "RowKey");
var rowKeyValueIndex = commandTextParts[rowKeyIndex + 2];
var rowKeyValue = rowKeyValueIndex == "null" ? null : parameters[int.Parse(rowKeyValueIndex.TrimStart('p'))].Value.ToString();
requestParameters.Add("RowKey=" + "'" + rowKeyValue + "'");
var partitionKeyIndex = Array.FindIndex(commandTextParts, s => s == "PartitionKey");
var partitionKeyValueIndex = commandTextParts[partitionKeyIndex + 2];
var partitionKeyValue = partitionKeyValueIndex == "null" ? null : parameters[int.Parse(partitionKeyValueIndex.TrimStart('p'))].Value.ToString();
requestParameters.Add("PartitionKey=" + "'" + partitionKeyValue + "'");
var uri = String.Format(settings.Uri.AbsoluteUri + "{0}({1})", tableName, String.Join(",", requestParameters.ToArray()));
var request = new TableStorageRequest(StorageHttpConstants.HttpMethod.Delete, uri, settings) { IfMatch = "*" };
return request;
}
public static bool Login(string UserName, string Password, out string TenNguoiDung)
{
bool HasRows = false; TenNguoiDung = string.Empty;
if (clsConnect.DB_OpenConnection("", "", "", ""))
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Users_Logins;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PMobNguoiDung;
DbParameter.Value = UserName;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PPasNguoiDung;
string EnPass = clsConnect.Encrypt(Password);
DbParameter.Value = EnPass;
DbParameters.Add(DbParameter);
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{
HasRows = DbDataReader.HasRows;
if (DbDataReader.Read())
{
TenNguoiDung = DbDataReader.GetString(DbDataReader.GetOrdinal(PTenNguoiDung));
}
else
{
TenNguoiDung = "Chưa xác định";
}
}
}
catch { }
}
}
return(HasRows);
}
private static void GetParameterOut(LBDbParameterCollection tsParms, System.Data.Common.DbParameterCollection dbParms, DataTable dtResult)
{
if (tsParms != null && tsParms.Count > 0)
{
foreach (LBDbParameter parm in tsParms)
{
if (parm.Direction != ParameterDirection.Input)
{
if (dtResult != null &&
dtResult.Rows.Count > 0 && dtResult.Columns.Contains(parm.ParameterName))
{
parm.Value = dtResult.Rows[0][parm.ParameterName];
}
else
{
parm.Value = dbParms["@" + parm.ParameterName].Value;
}
}
}
}
}
protected IDataMapper CreateDB_ForInsert()
{
_parameters = MockRepository.GenerateMock<DbParameterCollection>();
_command = MockRepository.GenerateMock<DbCommand>();
_command.Expect(c => c.Parameters).Return(_parameters);
_command.Stub(c => c.CommandText);
_command
.Expect(c => c.CreateParameter())
.Repeat.Any()
.Return(MockRepository.GenerateStub<DbParameter>());
_connection = MockRepository.GenerateMock<DbConnection>();
_connection.Expect(c => c.CreateCommand()).Return(_command);
_command.Expect(c => c.Connection).Return(_connection);
DbProviderFactory dbFactory = MockRepository.GenerateMock<DbProviderFactory>();
dbFactory.Expect(f => f.CreateConnection()).Return(_connection);
return new DataMapper(dbFactory, "connString...");
}
private void AttachParameters(DbParameterCollection dbParameters, object parameters)
{
var dic = parameters as IDictionary<string, object>;
if (dic != null)
{
foreach (var parameter in dic)
{
dbParameters.Add(CreateParameter(parameter.Key, parameter.Value));
}
}
else
{
var properties = parameters.GetType().GetRuntimeProperties();
foreach (var property in properties)
{
if (property.CanRead)
{
dbParameters.Add(CreateParameter(property.Name, property.GetValue(parameters)));
}
}
}
}
/// <summary>
/// Kiem tra xem so dien thoai khach hang da co tren he thong chua
/// </summary>
/// Tra ve: True - Da ton tại; False - Chua ton tai
public static bool m2018_CheckClient(string CodCardClient)
{
using (System.Data.Common.DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_CheckClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PCodCardClient;
DbParameter.Value = CodCardClient;
DbParameters.Add(DbParameter);
bool HasRows = false;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{ HasRows = DbDataReader.HasRows; }
}
catch { }
return(HasRows);
}
}
protected IDataMapper CreateDB_ForQuery(StubResultSet rs)
{
StubDataReader reader = new StubDataReader(rs);
_parameters = MockRepository.GenerateMock<DbParameterCollection>();
_parameters.Expect(p => p.Add(null)).Return(1).IgnoreArguments();
_command = MockRepository.GenerateMock<DbCommand>();
_command.Expect(c => c.ExecuteReader()).Return(reader);
_command.Expect(c => c.Parameters).Return(_parameters);
_command.Expect(c => c.CreateParameter()).Return(new System.Data.SqlClient.SqlParameter()).Repeat.Any();
_command.Stub(c => c.CommandText);
_connection = MockRepository.GenerateMock<DbConnection>();
_connection.Expect(c => c.CreateCommand()).Return(_command);
_command.Expect(c => c.Connection).Return(_connection);
DbProviderFactory dbFactory = MockRepository.GenerateMock<DbProviderFactory>();
dbFactory.Expect(f => f.CreateConnection()).Return(_connection);
return new DataMapper(dbFactory, "connString...");
}
/// <summary>
/// Executes the query as well as storeprocedure and returns the first column of the first row in the result.
/// All other columns and rows are ignored.
/// </summary>
/// <param name="strStoredProcedureName">Name of the STR stored procedure.</param>
/// <param name="objCommandType">Type of the obj command.</param>
/// <returns>Return object</returns>
public object ExecuteScalar(string strStoredProcedureName, CommandType objCommandType)
{
object objReturnValue = null;
try
{
this.OpenConnection();
this.objDBCommand.CommandText = strStoredProcedureName;
this.objDBCommand.CommandType = objCommandType;
objReturnValue = this.objDBCommand.ExecuteScalar();
return objReturnValue;
}
catch (Exception objEx)
{
throw objEx;
}
finally
{
this.objDbParameterCollection = this.objDBCommand.Parameters;
this.CloseConnection();
}
}
/// <summary>
/// Executes the StoredProcedure and returns the number of eaffected row..
/// </summary>
/// <param name="strStoredProcedureName">Name of the STR stored procedure.</param>
/// <param name="objCommandType">Type of the obj command.</param>
/// <returns>return number of effeted row</returns>
public int ExecuteNonQuery(string strStoredProcedureName, CommandType objCommandType)
{
try
{
this.OpenConnection();
this.objDBCommand.CommandText = strStoredProcedureName;
this.objDBCommand.CommandType = objCommandType;
return this.objDBCommand.ExecuteNonQuery();
}
catch (Exception objEx)
{
throw new Exception("Error while performing ExcuteQuery operation on " + strStoredProcedureName, objEx);
}
finally
{
this.objDbParameterCollection = this.objDBCommand.Parameters;
this.CloseConnection();
}
}
/// <summary>
/// It performs the DB operation and fetches the data from the database with the help of DataSet.
/// </summary>
/// <param name="strStoredProcedureName">Name of the stored procedure.</param>
/// <param name="objCommandType">Type of the CommandType.</param>
/// <returns>Return DataSet</returns>
public DataSet ExecuteDataSet(string strStoredProcedureName, CommandType objCommandType)
{
try
{
this.OpenConnection();
this.objDbDataAdapter = this.objDBProviderFactory.CreateDataAdapter(); //// Creating data adapter.
this.objDBCommand.CommandText = strStoredProcedureName; //// It may be an query and SP name
this.objDBCommand.CommandType = objCommandType; //// Type of command
this.objDbDataAdapter.SelectCommand = this.objDBCommand; //// Going to selete data from database
this.objResultDataSet = new DataSet();
this.objDbDataAdapter.Fill(this.objResultDataSet);
return this.objResultDataSet;
}
catch (Exception objEx)
{
throw new Exception("Error while performing ExecuteDataSet on " + strStoredProcedureName, objEx);
}
finally
{
this.objDbParameterCollection = this.objDBCommand.Parameters;
this.CloseConnection();
}
}
public abstract void CopyToParameterCollection(int commandIndex, DbParameterCollection destination);
public ParametersLogSerializer(System.Data.Common.DbParameterCollection coll)
{
this.Parameters = coll;
}
/// <summary>
/// Initialize a new instance of the class.
/// </summary>
/// <param name="command">Database command.</param>
public DbParameterCollection(DbCommand command)
{
_command = command;
_parameters = _command.Parameters;
}
internal static Exception ParameterNull(string parameter, DbParameterCollection collection, Type parameterType)
{
return(CollectionNullValue(parameter, collection.GetType(), parameterType));
}
//
// : IDataParameterCollection
//
static internal Exception ParametersMappingIndex(int index, DbParameterCollection collection)
{
return CollectionIndexInt32(index, collection.GetType(), collection.Count);
}
internal static Exception InvalidParameterType(DbParameterCollection collection, Type parameterType, object invalidValue)
{
return(CollectionInvalidType(collection.GetType(), parameterType, invalidValue));
}
/// <summary>
/// Gets the table storage request for the given settings and values
/// </summary>
public TableStorageRequest GetTableStorageRequest(TableStorageSettings settings, DbParameterCollection parameters)
{
var request = new TableStorageRequest(StorageHttpConstants.HttpMethod.Post, String.Format(settings.Uri.AbsoluteUri + "{0}", "Tables"), settings);
request.Body.AddProperty("TableName", tableName);
return request;
}
private DbParameter[] CreateCachableParamsClone(DbParameterCollection parms)
{
int i = 0;
DbParameter[] cacheParams = new DbParameter[parms.Count];
foreach (DbParameter param in parms)
{
cacheParams[i] = (DbParameter)((ICloneable)param).Clone();
cacheParams[i].Value = null;
i += 1;
}
return cacheParams;
}
protected override void AddParameter(DbParameterCollection collection, string name, object value)
{
collection.Add(new SqlParameter(name, value));
}
DbDataReader ExecuteResultSetReader(ViewPage page)
{
if (_serverRules.ResultSet == null)
return null;
SelectClauseDictionary expressions = new SelectClauseDictionary();
foreach (DataColumn c in _serverRules.ResultSet.Columns)
expressions[c.ColumnName] = c.ColumnName;
if (page.Fields.Count == 0)
{
PopulatePageFields(page);
EnsurePageFields(page, null);
}
DataView resultView = new DataView(_serverRules.ResultSet);
resultView.Sort = page.SortExpression;
using (DbConnection connection = CreateConnection(false))
{
DbCommand command = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
_resultSetParameters = command.Parameters;
expressions.Add("_DataView_RowFilter_", "true");
AppendFilterExpressionsToWhere(sb, page, command, expressions, String.Empty);
string filter = sb.ToString();
if (filter.StartsWith("where"))
filter = filter.Substring(5);
filter = Regex.Replace(filter, (Regex.Escape(_parameterMarker) + "\\w+"), DoReplaceResultSetParameter);
resultView.RowFilter = filter;
if (page.PageSize > 0)
page.TotalRowCount = resultView.Count;
}
if (RequiresPreFetching(page))
page.ResetSkipCount(true);
return resultView.ToTable().CreateDataReader();
}
private void Setup()
{
parametersList = new List<DbParameter>();
target = new DatabaseTraceListener_Accessor(
storedProcedureName, logConnectionStringName, null, null);
//target.connectionStringName = "TestConnectionString";
target.factory = MockRepository.GenerateStub<DbProviderFactory>();
target.connectionStringProvider = MockRepository.GenerateStub<IConfigurationValueProvider>();
target.connectionStringProvider.Stub((p) => p[target.connectionStringName]).
Return(target.connectionStringName);
connection = MockRepository.GenerateStub<DbConnection>();
command = MockRepository.GenerateStub<DbCommand>();
parameters = MockRepository.GenerateStub<DbParameterCollection>();
// Stubs for the connection and command
target.factory.Stub(
(f) => f.CreateConnection()).Return(connection);
target.factory.Stub((f) => f.CreateCommand()).Return(command);
// Definitely want to verify that Open is called
connection.Stub((c) => connection.Open());
// Unfortunately can't verify Dispose got called
connection.Stub((c) => connection.Dispose());
// Will verify later if ExecuteNonQuery is called
command.Stub((c) => command.ExecuteNonQuery()).Return(1);
command.Stub((c) => c.Dispose());
// Setup repetitive stub for the command parameters
//DbParameter parameter = null;
command.Stub((c) => c.Parameters).Repeat.Any().Return(parameters);
for (int i = 0; i < 12; i++)
{
DbParameter parameter = MockRepository.GenerateStub<DbParameter>();
target.factory.Stub((f) => f.CreateParameter()).Return(parameter);
parameters.Stub((p) => p.Add(parameter)).Do(new HandleParameterDelegate(HandleParameter));
}
}
public ParameterInliner(DbParameterCollection parameters)
{
DebugCheck.NotNull(parameters);
_parameters = parameters;
}
static internal Exception ParametersSourceIndex(string parameterName, DbParameterCollection collection, Type parameterType)
{
return CollectionIndexString(parameterType, ADP.ParameterName, parameterName, collection.GetType());
}
public abstract void CopyToParameterCollection (int commandIndex, DbParameterCollection destination);
static internal Exception ParameterNull(string parameter, DbParameterCollection collection, Type parameterType)
{
return CollectionNullValue(parameter, collection.GetType(), parameterType);
}
static internal Exception InvalidParameterType(DbParameterCollection collection, Type parameterType, object invalidValue)
{
return CollectionInvalidType(collection.GetType(), parameterType, invalidValue);
}
/// <summary>
/// It executes the Query/Stored Procedure and Executes the data from the database to data reader.
/// </summary>
/// <param name="strStoredProcedureName">Name of the STR stored procedure.</param>
/// <param name="objCommandType">Type of the obj command.</param>
/// <returns>Return IDataReader</returns>
public IDataReader ExecuteDataReader(string strStoredProcedureName, CommandType objCommandType)
{
try
{
this.OpenConnection();
this.objDBCommand.CommandType = objCommandType; ////Type of command
this.objDBCommand.CommandText = strStoredProcedureName; ////It may be an query and SP name
return this.objDBCommand.ExecuteReader();
}
catch (Exception objEx)
{
throw new Exception("Error while performing ExecuteDataReader operation on " + strStoredProcedureName, objEx);
}
finally
{
this.objDbParameterCollection = this.objDBCommand.Parameters;
}
}
internal static Exception ParametersSourceIndex(string parameterName, DbParameterCollection collection, Type parameterType)
{
return(CollectionIndexString(parameterType, ADP.ParameterName, parameterName, collection.GetType()));
}
//
// : IDataParameterCollection
//
internal static Exception ParametersMappingIndex(int index, DbParameterCollection collection)
{
return(CollectionIndexInt32(index, collection.GetType(), collection.Count));
}
