private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter, CommandType cmdType)
{
IDbDataParameter clone = new OracleParameter() { IsNullable = dbParameter.IsNullable };
// Remove leading ':' character for stored procedures.
if (cmdType == CommandType.StoredProcedure)
{
string name = parameterRenderer.RenderParameterName(dbParameter);
if (name.StartsWith(":", StringComparison.Ordinal))
name = name.Substring(1, name.Length - 1);
clone.ParameterName = name;
}
else
{
clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
}
if (dbParameter.PassMode == SpArgumentPassMode.DataTableFilledByAdapter)
((OracleParameter)clone).OracleDbType = OracleDbType.RefCursor;
else
clone.DbType = dbParameter.DbType;
clone.Direction = dbParameter.Direction;
clone.Precision = dbParameter.Precision;
clone.Scale = dbParameter.Scale;
clone.Size = dbParameter.Size;
clone.SourceColumn = dbParameter.SourceColumn;
clone.SourceVersion = dbParameter.SourceVersion;
clone.Value = dbParameter.Value;
return clone;
}
public static DbParameter[] DeriveParameters(DbConnection dbConnection, string spName)
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConn");
}
if (string.IsNullOrEmpty(spName))
{
throw new ArgumentNullException("spName");
}
DbCommand command = null;
using (DbConnection connection = (DbConnection) ((ICloneable) dbConnection).Clone())
{
command = connection.CreateCommand();
command.CommandText = spName;
command.CommandType = CommandType.StoredProcedure;
connection.Open();
if (!(command is SqlCommand))
{
throw new NotSupportedException();
}
SqlCommandBuilder.DeriveParameters((SqlCommand) command);
connection.Close();
}
if ((command.Parameters.Count > 0) && (command.Parameters[0].Direction == ParameterDirection.ReturnValue))
{
command.Parameters.RemoveAt(0);
}
DbParameter[] array = new DbParameter[command.Parameters.Count];
command.Parameters.CopyTo(array, 0);
return array;
}
public void updateYouTube(List<YouTube> youtubes)
{
DbParameter[] parameters = new DbParameter[3];
try
{
Ado.ExecuteNonQueryStoredProcedure("DeleteYouTubeUrls", null);
foreach (YouTube yt in youtubes)
{
parameters[0] = new SqlParameter("@YouTubeLink", yt.YOUTUBELINK);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.String;
parameters[1] = new SqlParameter("@NumberOfTimeExecuted", yt.NUMBEROFTIMEEXECUTED);
parameters[1].Direction = ParameterDirection.Input;
parameters[1].DbType = DbType.Int16;
parameters[2] = new SqlParameter("@YouTubeText", yt.YOUTUBETEXT);
parameters[2].Direction = ParameterDirection.Input;
parameters[2].DbType = DbType.String;
Ado.ExecuteNonQueryStoredProcedure("UpdateYouTubeUrls", parameters);
//lblError.Visible = true;
//lblError.Text = "Updated Successfully";
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
}
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server CE doesn't use sequences.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
// Auto ID must be fetched via SELECT after INSERT.
DbParameter autoId = null;
return autoId;
}
public static DbParameter[] CloneParameters(DbParameter[] dbParameters)
{
int length = dbParameters.Length;
DbParameter[] parameterArray = new DbParameter[length];
for (int i = 0; i < length; i++)
{
parameterArray[i] = (DbParameter)((ICloneable)dbParameters[i]).Clone();
}
return parameterArray;
}
/// <summary>
/// 添加SqlCommand 的参数
/// </summary>
/// <param name="CmdParameters">SqlCommand 的参数</param>
public void Add(DbParameter[] CmdParameters)
{
foreach (MySqlParameter parameter in CmdParameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) & Convert.IsDBNull(RuntimeHelpers.GetObjectValue(parameter.Value)))
{
parameter.Value = DBNull.Value;
}
this.Cmd.Parameters.Add(parameter);
}
}
public void UpdateImportDate(long id, DateTime importTime)
{
string sql = string.Format("update {0} set {1} = @{1} where {2} = @{2}", T_TableName,
C_ImportDate, C_Id);
DbParameter[] parameters = new DbParameter[]{
new MySqlParameter(string.Format("@{0}", C_ImportDate), importTime),
new MySqlParameter(string.Format("@{0}", C_Id), id)
};
this.ExecuteNonQuery(sql, parameters);
}
public void ArchiveMailsOlderThan(DateTime archiveDate)
{
string insertIntoArchive = "insert into " + archiveTableName + " SELECT * from " + tableName + " where " + sentColumn + "<@archiveDate";
DbParameter[] parameters = new DbParameter[] {
new DbParameter("@archiveDate", SqlDbType.DateTime, archiveDate)
};
dbTemplate.Update(insertIntoArchive, parameters);
string deleteMails = "delete from " + tableName + " where " + sentColumn + "<@archiveDate";
parameters = new DbParameter[] {
new DbParameter("@archiveDate", SqlDbType.DateTime, archiveDate)
};
dbTemplate.Update(deleteMails, parameters);
}
/// <summary>
/// A DbConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType, DbTransaction transaction)
{
using (DbCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
return command.ExecuteScalar();
}
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Parameter that contains the ID of the inserted row. <b>null</b> if auto-id field is not used.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
DbParameter autoId = null;
if (HasAutoIdField(insert.Table))
{
autoId = new DbParameter("___newAutoIdentity___", DbType.Int32) { Direction = ParameterDirection.Output };
if (dbms == DbmsType.SqlServer_7)
{
// @@IDENTITY
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = @@IDENTITY");
}
else
{
// scope_identity()
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = scope_identity()");
}
// BatchQuery (if ever implemented) reads this value to assign new ID value to InsertStatement.RetrievedData property.
//insert.NewIdOutputParameterIndexAfterRenderCompleted = parameters.Count - 1;
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter)
{
IDbDataParameter clone = new OleDbParameter();
((OleDbParameter)clone).IsNullable = dbParameter.IsNullable;
clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
clone.DbType = dbParameter.DbType;
clone.Direction = dbParameter.Direction;
clone.Precision = dbParameter.Precision;
clone.Scale = dbParameter.Scale;
clone.Size = dbParameter.Size;
clone.SourceColumn = dbParameter.SourceColumn;
clone.SourceVersion = dbParameter.SourceVersion;
clone.Value = dbParameter.Value;
return clone;
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable<dynamic> ExecuteExpandoObjects(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType, DbTransaction transaction)
{
using (DbCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
using (IDataReader reader = command.ExecuteReader())
{
return reader.ToExpandoObjects();
}
}
}
public void deleteAnswerByAnswerId(Guid guid)
{
DbParameter[] parameters = new DbParameter[1];
try
{
parameters[0] = new SqlParameter("@guid", guid);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.Guid;
Ado.ExecuteNonQueryStoredProcedure("sp_DeleteAnswer", parameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
}
}
private static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (DbParameter p in commandParameters)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
IDbColumn autoIdField = GetAutoIdField(insert.Table);
bool hasAutoIdField = (autoIdField != null);
bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);
AppendInsertIntoTableName(insert, dbms, output);
AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);
if (autoIdField != null && nextSequence != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
output.Append(" INTO ");
nextSequence.Render(dbms, output, parameters);
}
return nextSequence;
}
public DataTable getAllYouTubeLink()
{
DbParameter[] parameters = new DbParameter[1];
//parameters = null;
DataTable dtUser = new DataTable();
try
{
parameters[0] = new SqlParameter("@guid", null);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.Guid;
dtUser = Ado.ExecuteStoredProcedure("sp_GetAllYTlinks", parameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
dtUser.Dispose();
}
return dtUser;
}
private static void PrepareCommand(
DbCommand command,
DbConnection connection,
DbTransaction transaction,
CommandType commandType,
string commandText,
DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
command.CommandType = commandType;
command.CommandText = commandText;
command.Connection = connection;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
if (commandParameters != null) { AttachParameters(command, commandParameters); }
}
public DataTable getAllAnswer()
{
DbParameter[] parameters = new DbParameter[1];
//parameters = null;
DataTable dtAnswer = new DataTable();
try
{
parameters[0] = new SqlParameter("@blogId", BLOGID);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.Guid;
dtAnswer = Ado.ExecuteStoredProcedure("sp_GetAllAnswer", parameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
dtAnswer.Dispose();
}
return dtAnswer;
}
internal DbCommand BuildSelectCommand(DbProviderFactory factory, DbConnection connection, string commandText, ParameterCollection parameters, SqlDataSourceCommandType commandType)
{
DbCommand command = CreateCommand(factory, commandText, connection);
if ((parameters != null) && (parameters.Count > 0))
{
IOrderedDictionary values = parameters.GetValues(null, null);
string parameterPrefix = GetParameterPrefix(factory);
for (int i = 0; i < parameters.Count; i++)
{
Parameter parameter = parameters[i];
DbParameter parameter2 = CreateParameter(factory);
parameter2.ParameterName = parameterPrefix + parameter.Name;
if (parameter.DbType != DbType.Object)
{
SqlParameter parameter3 = parameter2 as SqlParameter;
if (parameter3 == null)
{
parameter2.DbType = parameter.DbType;
}
else if (parameter.DbType == DbType.Date)
{
parameter3.SqlDbType = SqlDbType.Date;
}
else if (parameter.DbType == DbType.Time)
{
parameter3.SqlDbType = SqlDbType.Time;
}
else
{
parameter2.DbType = parameter.DbType;
}
}
else
{
if ((parameter.Type != TypeCode.Empty) && (parameter.Type != TypeCode.DBNull))
{
parameter2.DbType = parameter.GetDatabaseType();
}
if ((parameter.Type == TypeCode.Empty) && ProviderRequiresDbTypeSet(factory))
{
parameter2.DbType = DbType.Object;
}
}
parameter2.Value = values[i];
if (parameter2.Value == null)
{
parameter2.Value = DBNull.Value;
}
if (Parameter.ConvertDbTypeToTypeCode(parameter2.DbType) == TypeCode.String)
{
if ((parameter2.Value is string) && (parameter2.Value != null))
{
parameter2.Size = ((string)parameter2.Value).Length;
}
else
{
parameter2.Size = 1;
}
}
command.Parameters.Add(parameter2);
}
}
command.CommandType = GetCommandType(commandType);
return(command);
}
/// <summary>
/// A DbConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this DbConnection @this, string cmdText, DbParameter[] parameters, DbTransaction transaction)
{
return @this.ExecuteScalar(cmdText, parameters, CommandType.Text, transaction);
}
private void PrepareCommand(DbCommand cmd, DbConnection conn, SqlTransaction trans, string cmdText, DbParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (DbParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
protected override void SetParameter(string parameterName, DbParameter value)
{
}
public int AddParameter(DbParameter parameter)
{
return(objCommand.Parameters.Add(parameter));
}
protected override void ApplyParameterInfo(DbParameter parameter, DataRow row,
StatementType statementType, bool whereClause)
{
((MySqlParameter)parameter).MySqlDbType = (MySqlDbType)row["ProviderType"];
}
public void saveAnswer()
{
DbParameter[] parameters = new DbParameter[3];
try
{
parameters[0] = new SqlParameter("@Answer", ANSWER);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.String;
parameters[1] = new SqlParameter("@CreatedBy", CREATEDBY);
parameters[1].Direction = ParameterDirection.Input;
parameters[1].DbType = DbType.Guid;
parameters[2] = new SqlParameter("@BlogId", BLOGID);
parameters[2].Direction = ParameterDirection.Input;
parameters[2].DbType = DbType.Guid;
Ado.ExecuteNonQueryStoredProcedure("sp_AddNewAnswer", parameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
}
}
public static ICollection <T> SqlQuery <T>(this DbContext db, CommandType type, RawSqlString SQL, params object[] parameters)
{
var conn = db.Database.GetDbConnection();
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
#region Set CMD
cmd.CommandType = type;
cmd.CommandText = SQL.Format;
#region Set Params
if (parameters != null && parameters.Length > 0)
{
if (parameters[0].GetType() == typeof(string))
{
int i = 0;
foreach (var param in parameters)
{
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@p" + i++;
p.DbType = DbType.String;
p.Value = param;
cmd.Parameters.Add(p);
}
}
else
{
{
foreach (SqlParameter item in parameters as SqlParameter[])
{
DbParameter p = cmd.CreateParameter();
p.DbType = item.DbType;
p.ParameterName = item.ParameterName;
p.Value = item.Value;
cmd.Parameters.Add(p);
}
}
}
}
#endregion
#endregion
var rtnList = new List <T>();
object val = new object();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rtnList.Add((T)reader.GetValue(0));
}
}
return(rtnList);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
// Update an existing product
public static bool UpdateProduct(string productId, string name, string description, string price,
string Thumbnail, string Image, string PromoDept, string PromoFront)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogUpdateProduct";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ProductId";
param.Value = productId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@ProductName";
param.Value = name;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@ProductDescription";
param.Value = description;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Price";
param.Value = price;
param.DbType = DbType.Decimal;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Thumbnail";
param.Value = Thumbnail;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Image";
param.Value = Image;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PromoDept";
param.Value = PromoDept;
param.DbType = DbType.Boolean;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PromoFront";
param.Value = PromoFront;
param.DbType = DbType.Boolean;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in GenericDataAccess, we ignore them here
}
// result will be 1 in case of success
return(result != -1);
}
// Search the product catalog
public static DataTable Search(string searchString, string allWords, string pageNumber, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "SearchCatalog";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DescriptionLength";
param.Value = CdShopConfiguration.ProductDescriptionLength;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@AllWords";
param.Value = allWords.ToUpper() == "TRUE" ? "1" : "0";
param.DbType = DbType.Byte;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = pageNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@ProductsPerPage";
param.Value = CdShopConfiguration.ProductsPerPage;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@HowManyResults";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// define the maximum number of words
int howManyWords = 5;
// transform search string into array of words
string[] words = Regex.Split(searchString, "[^a-zA-Z0-9а-яА-Я]+");
// add the words as stored procedure parameters
int index = 1;
for (int i = 0; i <= words.GetUpperBound(0) && index <= howManyWords; i++)
{
// ignore short words
if (words[i].Length > 2)
{
// create the @Word parameters
param = comm.CreateParameter();
param.ParameterName = "@Word" + index.ToString();
param.Value = words[i];
param.DbType = DbType.String;
comm.Parameters.Add(param);
index++;
}
}
// execute the stored procedure and save the results in a DataTable
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// calculate how many pages of products and set the out parameter
int howManyProducts =
Int32.Parse(comm.Parameters["@HowManyResults"].Value.ToString());
howManyPages = (int)Math.Ceiling((double)howManyProducts / CdShopConfiguration.ProductsPerPage);
return(table);
}
protected override void SetParameter(string index, DbParameter value)
{
parameters [IndexOf(value)] = (TParameter)value;
}
protected override void SetParameter(int index, DbParameter value)
{
parameters [index] = (TParameter)value;
}
private static OneRow[] RIndexOnInt_PrepareSourceTable(string sourcetablename, int testsize)
{
string[] words = @"Apple's good for you. $apple$ with 2 dollar signs. Whether you want to build an audience or
communicate with your closest friends, the MySpace Privacy Control Panel allows you to control what
information is shared, who can contact you, how you are notified and the tools to eliminate unwanted
spam. Here you control who can see you are online, who knows your birthday, who can view your profile,
who can share your photos and how to block specific users and users by age. Here you control spam security
levels, who can message you, who can request to be friends, approval of comments, group invitations, event and
IM invitations. Here you control your subscription to the MySpace newsletter featuring exclusive content and
promotions and email notifications about friend requests, comments, blog subscriptions and event invitations.
Here you control SMS (text message) alerts for MySpace messages, friend requests, content and when you
receive mobile content subscriptions. Here you control how your calendar is set up and how you are reminded
of important events. Here you control how groups are displayed on your profile, if and where you accept HTML
comments, how your music player works and your away message when you are on vacation or are
taking a break.".Split(new string[] { " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
for (int wi = 0; wi < words.Length; wi++)
{
words[wi] = words[wi].Trim();
if (words[wi].Length == 0)
{
words[wi] = "xx";
}
}
Dictionary <int, OneRow> testrows = new Dictionary <int, OneRow>();
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
Console.WriteLine("Preparing source table...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + sourcetablename + " (num1 INT, num2 DOUBLE, str CHAR(200), dt DATETIME, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + sourcetablename + " VALUES (@num1, @num2, @str, @dt, @num3)";
DbParameter num1 = cmd.CreateParameter();
num1.ParameterName = "@num1";
num1.DbType = DbType.Int32;
DbParameter num2 = cmd.CreateParameter();
num2.ParameterName = "@num2";
num2.DbType = DbType.Double;
DbParameter str = cmd.CreateParameter();
str.ParameterName = "@str";
str.DbType = DbType.String;
str.Size = 200;
DbParameter dt = cmd.CreateParameter();
dt.ParameterName = "@dt";
dt.DbType = DbType.DateTime;
DbParameter num3 = cmd.CreateParameter();
num3.ParameterName = "@num3";
num3.DbType = DbType.Int64;
Random rnd = new Random(System.DateTime.Now.Millisecond / 2 + System.Diagnostics.Process.GetCurrentProcess().Id / 2);
int rowcount = 5000;// (1024 * 1024 * 10) / (9 * 3);
int min = -50;
int max = 50;
DateTime dtseed = DateTime.Now;
for (int i = 0; i < rowcount; i++)
{
OneRow row;
row.num1 = rnd.Next(min, max);
row.num2 = rnd.Next(min, max);
row.num3 = rnd.Next(min, max);
row.dt = dtseed.AddDays(rnd.Next(min, max));
row.str = words[rnd.Next() % words.Length];
if (testrows.Count < testsize && !testrows.ContainsKey(row.num1))
{
testrows.Add(row.num1, row);
}
num1.Value = row.num1;
num2.Value = row.num2;
num3.Value = row.num3;
str.Value = row.str;
dt.Value = row.dt;
cmd.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine("Source table created.");
return((new List <OneRow>(testrows.Values)).ToArray());
}
public string RenderParameterName(DbParameter parameter)
{
return ":" + parameter.ParameterName;
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable<dynamic> ExecuteExpandoObjects(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType)
{
return @this.ExecuteExpandoObjects(cmdText, parameters, commandType, null);
}
public abstract void Add(DbParameter objDbParameter);
protected override bool SkipParameter(DbParameter parameter) {
return ((OracleParameter)parameter).OracleType == OracleType.Cursor;
}
/// <summary>
/// 导入历史数据 --- 病种组合维护
/// </summary>
/// <auth>Yanqiao.Cai</auth>
/// <date>2013-01-07</date>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_importData_Click(object sender, EventArgs e)
{
try
{
if (DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show("您确定要导入历史数据吗？", "导入历史数据", DrectSoft.Common.Ctrs.DLG.MyMessageBoxButtons.OkCancel) == DialogResult.Cancel)
{
return;
}
#region 导入原病种组合
string sqlStr1 = " select id,diseasegroup,name from DiseaseGroup ";
DS_SqlHelper.CreateSqlHelper();
DataTable dt1 = DS_SqlHelper.ExecuteDataTable(sqlStr1, CommandType.Text);
if (null != dt1 && dt1.Rows.Count > 0)
{
foreach (DataRow drow in dt1.Rows)
{
List <DbParameter> parameters = new List <DbParameter>();
SqlParameter param1 = new SqlParameter("@name", SqlDbType.Char);
SqlParameter param2 = new SqlParameter("@py", SqlDbType.Char);
SqlParameter param3 = new SqlParameter("@wb", SqlDbType.Char);
//名称、拼音、五笔
string groupName = null == drow["NAME"] ? "" : drow["NAME"].ToString();
if (string.IsNullOrEmpty(groupName))
{
continue;
}
GenerateShortCode shortCode = new GenerateShortCode(m_app.SqlHelper);
string[] code = shortCode.GenerateStringShortCode(groupName);
param1.Value = groupName;
parameters.Add(param1);
if (null != code && code.Length >= 2)
{
param2.Value = null == code[0] ? string.Empty : code[0].ToString();
param3.Value = null == code[1] ? string.Empty : code[1].ToString();
parameters.Add(param2);
parameters.Add(param3);
}
//病种IDs (Y58.501$白喉疫苗接种反应，E70.301$白化病，E25.001$11-羟化酶缺陷，Y58.601$百日咳疫苗反应)
string IDs = string.Empty;
string IDAndNames = null == drow["diseasegroup"] ? "" : drow["diseasegroup"].ToString();
if (string.IsNullOrEmpty(IDAndNames))
{
continue;
}
string[] IDAndNameArray = IDAndNames.Split('，');
if (null != IDAndNameArray && IDAndNameArray.Length > 0)
{
foreach (string IDAndName in IDAndNameArray)
{
string[] str = IDAndName.Split('$');
if (null != str && str.Length > 0)
{
IDs += str[0] + "$";
}
}
if (!string.IsNullOrEmpty(IDs))
{
IDs = IDs.Substring(0, IDs.Length - 1);
}
}
SqlParameter param4 = new SqlParameter("@diseaseids", SqlDbType.Char);
param4.Value = IDs;
parameters.Add(param4);
//是否有效
SqlParameter param5 = new SqlParameter("@valid", SqlDbType.Int);
param5.Value = 1;
parameters.Add(param5);
//创建人
SqlParameter param6 = new SqlParameter("@create_user", SqlDbType.Char);
param6.Value = DS_Common.currentUser.Id;
parameters.Add(param6);
//创建时间
SqlParameter param7 = new SqlParameter("@create_time", SqlDbType.Char);
param7.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
parameters.Add(param7);
DS_SqlService.InsertDiseaseGroup(parameters);
}
DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show("病种组合历史数据导入成功");
}
else
{
DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show("病种组合不存在历史数据");
}
#endregion
#region 导入原用户&病种组合关系
int newNum = 1;
string sqlStr2 = " select id,name,PY,WB,DEPTID,RELATEDISEASE from attendingphysician ";
DS_SqlHelper.CreateSqlHelper();
DataTable dt2 = DS_SqlHelper.ExecuteDataTable(sqlStr2, CommandType.Text);
if (null != dt2 && dt2.Rows.Count > 0)
{
foreach (DataRow dr in dt2.Rows)
{
List <DbParameter> pars = new List <DbParameter>();
//匹配组合ID (Y58.601$百日咳疫苗反应，A37.905$百日咳脑病，E25.002$17-羟化酶缺陷)
string IDs = string.Empty;
string IDAndNames = null == dr["RELATEDISEASE"] ? "" : dr["RELATEDISEASE"].ToString();
if (string.IsNullOrEmpty(IDAndNames))
{
continue;
}
string[] IDAndNameArray = IDAndNames.Split('，');
if (null != IDAndNameArray && IDAndNameArray.Length > 0)
{
foreach (string IDAndName in IDAndNameArray)
{
string[] str = IDAndName.Split('$');
if (null != str && str.Length > 0)
{
IDs += str[0] + "$";
}
}
if (!string.IsNullOrEmpty(IDs))
{
IDs = IDs.Substring(0, IDs.Length - 1);
}
}
string sqlStr3 = " select * from DiseasesGroup where diseaseids = @diseaseids ";
DbParameter[] sqlParams = new DbParameter[]
{
new SqlParameter("@diseaseids", SqlDbType.Char)
};
sqlParams[0].Value = IDs;
DS_SqlHelper.CreateSqlHelper();
DataTable dt3 = DS_SqlHelper.ExecuteDataTable(sqlStr3, sqlParams, CommandType.Text);
SqlParameter par1 = new SqlParameter("@groupids", SqlDbType.Char);
if (null != dt3 && dt3.Rows.Count > 0)
{//该用户对应组合存在
par1.Value = null == dt3.Rows[0]["ID"] ? "" : dt3.Rows[0]["ID"].ToString();
}
else
{//该用户对应组合不存在,此时需新增组合
#region 新增组合
string newGroupID = AddDiseaseGroupUnknow(newNum, IDs);
newNum++;
if (!string.IsNullOrEmpty(newGroupID))
{
par1.Value = newGroupID;
}
#endregion
}
pars.Add(par1);
SqlParameter par2 = new SqlParameter("@userid", SqlDbType.Char);
par2.Value = null == dr["ID"] ? "" : dr["ID"].ToString();
if (string.IsNullOrEmpty(par2.Value.ToString()))
{
continue;
}
pars.Add(par2);
SqlParameter par3 = new SqlParameter("@username", SqlDbType.Char);
par3.Value = null == dr["NAME"] ? "" : dr["NAME"].ToString();
pars.Add(par3);
SqlParameter par4 = new SqlParameter("@valid", SqlDbType.Int);
par4.Value = 1;
pars.Add(par4);
SqlParameter par5 = new SqlParameter("@create_user", SqlDbType.Char);
par5.Value = DS_Common.currentUser.Id;
pars.Add(par5);
SqlParameter par6 = new SqlParameter("@create_time", SqlDbType.Char);
par6.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
pars.Add(par6);
DS_SqlService.InsertUserMatchDiseaseGroup(pars);
}
DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show("用户权限历史数据导入成功");
}
else
{
DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show("用户权限不存在历史数据");
}
#endregion
//隐藏导入历史数据按钮
this.btn_importData.Visible = false;
string hideSql = " update appcfg set value = '<mainDoc><isnew>1</isnew><btnflag>0</btnflag></mainDoc>' where configkey = 'ImportDiseasesGroupHistoryData' ";
DS_SqlHelper.CreateSqlHelper();
DS_SqlHelper.ExecuteNonQuery(hideSql, CommandType.Text);
RefreashGroupsData();
}
catch (Exception ex)
{
DrectSoft.Common.Ctrs.DLG.MyMessageBox.Show(1, ex);
}
}
public static void RSelect()
{
string tablename = "rselect_test_" + Guid.NewGuid().ToString().Replace("-", "");
string tablenameSorted = "rselect_test_sorted" + Guid.NewGuid().ToString().Replace("-", "");
string indexname = Guid.NewGuid().ToString().Replace("-", "") + "apple";
Dictionary <long, List <KeyValuePair <long, long> > > expected = new Dictionary <long, List <KeyValuePair <long, long> > >();
Dictionary <long, int> testvalues = new Dictionary <long, int>(5);
const int TESTSIZE = 5;
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("DSpace_DataProvider");
{
Console.WriteLine("Preparing data...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablename + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablename + " VALUES (@num1, @num2, @num3)";
DbParameter num1 = cmd.CreateParameter();
num1.ParameterName = "@num1";
num1.DbType = DbType.Int64;
DbParameter num2 = cmd.CreateParameter();
num2.ParameterName = "@num2";
num2.DbType = DbType.Int64;
DbParameter num3 = cmd.CreateParameter();
num3.ParameterName = "@num3";
num3.DbType = DbType.Int64;
Random rnd = new Random(System.DateTime.Now.Millisecond / 2 + System.Diagnostics.Process.GetCurrentProcess().Id / 2);
int rowcount = 5000;// (1024 * 1024 * 10) / (9 * 3);
int min = -50;
int max = 50;
for (int i = 0; i < rowcount; i++)
{
long key = rnd.Next(min, max);
if (testvalues.Count < TESTSIZE && !testvalues.ContainsKey(key))
{
testvalues.Add(key, 0);
}
num1.Value = key;
num2.Value = rnd.Next(min, max);
num3.Value = rnd.Next(min, max);
cmd.ExecuteNonQuery();
}
}
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE " + tablenameSorted + " (num1 LONG, num2 LONG, num3 LONG)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tablenameSorted + " SELECT * FROM " + tablename + " ORDER BY num1";
cmd.ExecuteNonQuery();
}
///////! cannot create index here.
conn.Close();
Console.WriteLine("Data prepared.");
}
{
Console.WriteLine("Querying data using SELECT...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (long key in testvalues.Keys)
{
cmd.CommandText = "select * from " + tablenameSorted + " where num1 = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
if (!expected.ContainsKey(num1))
{
expected.Add(num1, new List <KeyValuePair <long, long> >());
}
expected[num1].Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
}
conn.Close();
Console.WriteLine("SELECT completed.");
}
{
Console.WriteLine("Creating RIndexes...");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE RINDEX " + indexname + " FROM " + tablenameSorted;
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("RIndexes created.");
}
Console.WriteLine("Querying data using RSELECT...");
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost; rindex=nopool";
conn.Open();
DbCommand cmd = conn.CreateCommand();
foreach (long key in expected.Keys)
{
List <KeyValuePair <long, long> > results = new List <KeyValuePair <long, long> >();
cmd.CommandText = "rselect * from " + indexname.ToUpper() + " where key = " + key.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long num1 = reader.GetInt64(0);
long num2 = reader.GetInt64(1);
long num3 = reader.GetInt64(2);
results.Add(new KeyValuePair <long, long>(num2, num3));
}
reader.Close();
//compare results
List <KeyValuePair <long, long> > xlist = expected[key];
if (xlist.Count != results.Count)
{
throw new Exception("Result count: " + results.Count.ToString() + " is different from that of expected: " + xlist.Count.ToString());
}
foreach (KeyValuePair <long, long> rpair in results)
{
bool found = false;
foreach (KeyValuePair <long, long> xpair in xlist)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect returned a row which was not located in expected results. num2=" + rpair.Key.ToString() + " num3=" + rpair.Value.ToString());
}
}
foreach (KeyValuePair <long, long> xpair in xlist)
{
bool found = false;
foreach (KeyValuePair <long, long> rpair in results)
{
if (rpair.Key == xpair.Key && rpair.Value == xpair.Value)
{
found = true;
break;
}
}
if (!found)
{
throw new Exception("RSelect did not return an expected row. num2=" + xpair.Key.ToString() + " num3=" + xpair.Value.ToString());
}
}
}
conn.Close();
}
Console.WriteLine("RSelect completed.");
{
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "drop table " + tablename;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table " + tablenameSorted;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop rindex " + indexname;
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// 新增病种组合 --- 导入历史数据专用
/// </summary>
/// <param name="newNum">未知组合序号</param>
/// <param name="IDs">病种ID集合</param>
/// <returns></returns>
private string AddDiseaseGroupUnknow(int newNum, string IDs)
{
try
{
if (string.IsNullOrEmpty(IDs))
{
return(string.Empty);
}
List <DbParameter> paramets = new List <DbParameter>();
SqlParameter pa1 = new SqlParameter("@name", SqlDbType.Char);
SqlParameter pa2 = new SqlParameter("@py", SqlDbType.Char);
SqlParameter pa3 = new SqlParameter("@wb", SqlDbType.Char);
//名称、拼音、五笔
string groupNameNew = "未知组合" + newNum.ToString();
GenerateShortCode shortCode = new GenerateShortCode(m_app.SqlHelper);
string[] code = shortCode.GenerateStringShortCode(groupNameNew);
pa1.Value = groupNameNew;
paramets.Add(pa1);
if (null != code && code.Length >= 2)
{
pa2.Value = null == code[0] ? string.Empty : code[0].ToString();
pa3.Value = null == code[1] ? string.Empty : code[1].ToString();
paramets.Add(pa2);
paramets.Add(pa3);
}
//病种IDs
SqlParameter param4 = new SqlParameter("@diseaseids", SqlDbType.Char);
param4.Value = IDs;
paramets.Add(param4);
//是否有效
SqlParameter param5 = new SqlParameter("@valid", SqlDbType.Int);
param5.Value = 1;
paramets.Add(param5);
//创建人
SqlParameter param6 = new SqlParameter("@create_user", SqlDbType.Char);
param6.Value = DS_Common.currentUser.Id;
paramets.Add(param6);
//创建时间
SqlParameter param7 = new SqlParameter("@create_time", SqlDbType.Char);
param7.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
paramets.Add(param7);
DS_SqlService.InsertDiseaseGroup(paramets);
string newGroupID = string.Empty;
string sqlStr = " select * from DiseasesGroup where diseaseids = @diseaseids ";
DbParameter[] sqlParams = new DbParameter[]
{
new SqlParameter("@diseaseids", SqlDbType.Char)
};
sqlParams[0].Value = IDs;
DS_SqlHelper.CreateSqlHelper();
DataTable dt = DS_SqlHelper.ExecuteDataTable(sqlStr, sqlParams, CommandType.Text);
if (null != dt && dt.Rows.Count > 0)
{
newGroupID = dt.Rows[0]["ID"].ToString();
}
return(newGroupID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected override void SetParameter(int index, DbParameter value)
{
}
public override void BindULong(DbParameter parameter, object value)
{
parameter.DbType = DbType.String;
parameter.Value = value ?? DBNull.Value;
}
protected override void SetParameter(int index, DbParameter value) => throw new NotImplementedException();
public int AddParameter(DbParameter parameter)
{
return objCommand.Parameters.Add(parameter);
}
public abstract void AddParameter(string typeName, string name, object value, DbCommand dbCommand, DbParameter dbParameter);
/// <summary>
/// 构建没有主键的表Adapter 暂时没有update delete
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private DbDataAdapter CreateAdapter(DataTable dt)
{
DbDataAdapter adapter = this.DbProviderFactoryInstance.CreateDataAdapter();
DataTableMapping tableMapping = new DataTableMapping();
tableMapping.SourceTable = "Table";
tableMapping.DataSetTable = dt.TableName;
DbCommand insertCommand = this.DbProviderFactoryInstance.CreateCommand();
DbCommand updateCommand = this.DbProviderFactoryInstance.CreateCommand();
DbCommand deleteCommand = this.DbProviderFactoryInstance.CreateCommand();
insertCommand.CommandType = CommandType.Text;
string parameterPre = this.DbHelperTypeEnum == DbHelperType.Oracle ? ":" : "@"; //参数前缀
string temp1 = "";
string temp2 = "";
string temp3 = ""; //更新语句
string temp4 = ""; //更新语句
foreach (DataColumn column in dt.Columns)
{
tableMapping.ColumnMappings.Add(column.ColumnName, column.ColumnName);
temp1 += "[" + column.ColumnName + "],";
temp2 += parameterPre + column.ColumnName + ",";
if (column.AllowDBNull == false && column.Unique)
{
temp4 = "and " + column.ColumnName + "=" + parameterPre + column.ColumnName + ",";
}
else
{
if (column.AutoIncrement == false) //如果该行不自动递增
{
temp3 = column.ColumnName + "=" + parameterPre + column.ColumnName + ",";
}
}
DbParameter para = this.DbProviderFactoryInstance.CreateParameter();
para.ParameterName = parameterPre + column.ColumnName;
para.SourceColumn = column.ColumnName;
para.SourceVersion = DataRowVersion.Current;
insertCommand.Parameters.Add(para);
DbParameter para2 = this.DbProviderFactoryInstance.CreateParameter();
para2.ParameterName = parameterPre + column.ColumnName;
para2.SourceColumn = column.ColumnName;
para2.SourceVersion = DataRowVersion.Current;
updateCommand.Parameters.Add(para2);
// insertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PFP", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, 0, 0, "PFP", System.Data.DataRowVersion.Current, false, null, "", "", ""));
}
temp1 = temp1.TrimEnd(',');
temp2 = temp2.TrimEnd(',');
temp3 = temp3.TrimEnd(',');
temp4 = temp4.TrimEnd(',');
//构造插入语句
insertCommand.CommandText = " insert into [" + dt.TableName + "] (" + temp1 + ") values (" + temp2 + ")";
updateCommand.CommandText = " update [" + dt.TableName + "] set " + temp3 + " where 1=1 " + temp4;
adapter.TableMappings.Add(tableMapping);
adapter.DeleteCommand = deleteCommand;
adapter.InsertCommand = insertCommand;
adapter.UpdateCommand = updateCommand;
return(adapter);
}
/// <summary>
/// A DbConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType)
{
return @this.ExecuteScalar(cmdText, parameters, commandType, null);
}
protected override void ApplyParameterInfo(DbParameter parameter, DataRow row, StatementType statementType, bool whereClause)
{
((SQLiteParameter)parameter).DbType = (DbType)row[SchemaTableColumn.ProviderType];
}
/// <summary>
/// A DbConnection extension method that executes the expando object operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <returns>A dynamic.</returns>
public static dynamic ExecuteExpandoObject(this DbConnection @this, string cmdText, DbParameter[] parameters)
{
return @this.ExecuteExpandoObject(cmdText, parameters, CommandType.Text, null);
}
protected override void ConfigureParameter(DbParameter parameter)
{
base.ConfigureParameter(parameter);
((OracleParameter)parameter).OracleDbType = OracleDbType.TimeStampTZ;
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable<dynamic> ExecuteExpandoObjects(this DbConnection @this, string cmdText, DbParameter[] parameters, DbTransaction transaction)
{
return @this.ExecuteExpandoObjects(cmdText, parameters, CommandType.Text, transaction);
}
/// <summary>
///
/// </summary>
/// <param name="parameterName"></param>
/// <param name="value"></param>
protected override void SetParameter(string parameterName, DbParameter value)
{
throw new NotImplementedException();
}
public virtual void AddParameter(DbCommand command, string name, SqlDbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
DbParameter parameter = this.CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value);
command.Parameters.Add(parameter);
}
public bool SaveToDataBase(CardServiceModel model)
{
try
{
MysqlHelper dbhelper = new MysqlHelper("name=parklotManager", "MySql.Data.MySqlClient");
//临时车
string commandtext = @"replace into t_tempcard(projectGuid,identifying,parkCode,carNo,carOwnerName,mobile,carTypeGuid,remark,enable,locked,startDate,endDate,pauseDate,continueDate,drivewayListContent,rechargeOperator)
values(@projectGuid,@identifying,@parkCode,@carNo,@carOwnerName,@mobile,@carTypeGuid,@remark,@enable,@locked,@startDate,@endDate,@pauseDate,@continueDate,@drivewayListContent,@rechargeOperator)";
DbParameter projectGuid = dbhelper.factory.CreateParameter();
projectGuid.ParameterName = "@projectGuid";
projectGuid.Value = model.ProjectGuid;
DbParameter identifying = dbhelper.factory.CreateParameter();
identifying.ParameterName = "@identifying";
identifying.Value = model.ParkCode + Convert.ToBase64String(Encoding.UTF8.GetBytes(model.CarNo));
DbParameter parkCode = dbhelper.factory.CreateParameter();
parkCode.ParameterName = "@parkCode";
parkCode.Value = model.ParkCode;
DbParameter carNo = dbhelper.factory.CreateParameter();
carNo.ParameterName = "@carNo";
carNo.Value = model.CarNo;
DbParameter carOwnerName = dbhelper.factory.CreateParameter();
carOwnerName.ParameterName = "@carOwnerName";
carOwnerName.Value = model.CarOwnerName;
DbParameter mobile = dbhelper.factory.CreateParameter();
mobile.ParameterName = "@mobile";
mobile.Value = model.Mobile;
DbParameter carTypeGuid = dbhelper.factory.CreateParameter();
carTypeGuid.ParameterName = "@carTypeGuid";
carTypeGuid.Value = model.CarTypeGuid;
DbParameter remark = dbhelper.factory.CreateParameter();
remark.ParameterName = "@remark";
remark.Value = model.Remark;
DbParameter enable = dbhelper.factory.CreateParameter();
enable.ParameterName = "@enable";
enable.Value = model.Enable;
DbParameter locked = dbhelper.factory.CreateParameter();
locked.ParameterName = "@locked";
locked.Value = model.Locked;
DbParameter startDate = dbhelper.factory.CreateParameter();
startDate.ParameterName = "@startDate";
startDate.Value = model.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
DbParameter endDate = dbhelper.factory.CreateParameter();
endDate.ParameterName = "@endDate";
endDate.Value = model.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
DbParameter pauseDate = dbhelper.factory.CreateParameter();
pauseDate.ParameterName = "@pauseDate";
pauseDate.Value = model.PauseDate.ToString("yyyy-MM-dd HH:mm:ss");
DbParameter continueDate = dbhelper.factory.CreateParameter();
continueDate.ParameterName = "@continueDate";
continueDate.Value = model.ContinueDate.ToString("yyyy-MM-dd HH:mm:ss");
DbParameter drivewayListContent = dbhelper.factory.CreateParameter();
drivewayListContent.ParameterName = "@drivewayListContent";
drivewayListContent.Value = m_serializer.Serialize(model.DrivewayGuidList ?? new List <string>());
DbParameter rechargeOperator = dbhelper.factory.CreateParameter();
rechargeOperator.ParameterName = "@rechargeOperator";
rechargeOperator.Value = model.RechargeOperator;
DbParameter[] parameter = new DbParameter[] { projectGuid, identifying, parkCode, carNo, carOwnerName, mobile, carTypeGuid, remark, enable, locked, startDate, endDate, pauseDate, continueDate, drivewayListContent, rechargeOperator };
return(dbhelper.ExecuteNonQuery(commandtext, parameter) > 0 ? true : false);
}
catch (Exception ex)
{
m_logger.LogFatal(LoggerLogicEnum.DataService, "", "", "", "Fujica.com.cn.DataService.TempCardServicePersistent.SaveToDataBase", string.Format("保存卡信息时发生异常，入参:{0}", m_serializer.Serialize(model)), ex.ToString());
}
return(false);
}
public void updateAnswer()
{
DbParameter[] parameters = new DbParameter[2];
try
{
parameters[0] = new SqlParameter("@guid", ID);
parameters[0].Direction = ParameterDirection.Input;
parameters[0].DbType = DbType.Guid;
parameters[1] = new SqlParameter("@Answer", ANSWER);
parameters[1].Direction = ParameterDirection.Input;
parameters[1].DbType = DbType.String;
Ado.ExecuteNonQueryStoredProcedure("sp_UpdateAnswer", parameters);
}
catch (Exception ex)
{
throw ex;
}
finally
{
parameters = null;
}
}
public static bool InsertSalesInvoice(Int32 SoID, DateTime InvoiceDate, string InvoiceNo,
double taxAmount, double totalAmount,
string Catagory, string Product, Int32 InQuantity, double InPrice, int EnterBy)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_InvoiceDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = SoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvoiceNo";
param.Value = InvoiceNo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@taxamount";
param.Value = taxAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@totalAmount";
param.Value = totalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Catagory";
param.Value = Catagory;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Product";
param.Value = Product;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InQuantity";
param.Value = InQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InPrice";
param.Value = InPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
return(result != -1);
}
public static bool InsertSalesInvoicedetails(Int32 Invoiceid, Int32 PoID, Int32 Catagory, Int32 Product,
Int32 POQuantity, double POPrice, double POtax, double POtotalAmount,
Int32 InQuantity, double InPrice, string Intax, double INtotalAmount, Int32 EnterBy, string loaction, string LblProduct, DateTime InvoiceDate)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_InvoiceDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvocieID";
param.Value = Invoiceid;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = PoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Catagory";
param.Value = Catagory;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Product";
param.Value = Product;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POQuantity";
param.Value = POQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POPrice";
param.Value = POPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POtax";
param.Value = POtax;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@POtotalamoun";
param.Value = POtotalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InQuantity";
param.Value = InQuantity;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InPrice";
param.Value = InPrice;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@INtax";
param.Value = Intax;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@INtotalamount";
param.Value = INtotalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@loaction";
param.Value = loaction;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@LblProduct";
param.Value = LblProduct;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
return(result != -1);
}
public static bool InsertSalesInvoice(out int invoiceID, Int32 PoID, string PONo, DateTime PODate,
string InvoiceNo, DateTime InvoiceDate, double totalAmount, int EnterBy)
{
// get a configured DbCommand object
DbCommand comm = DBLayer.CreateCommand();
// set the stored procedure name
comm.CommandText = "USB_Insert_So_Invoice";
// create a new parameter
DbParameter param = comm.CreateParameter();
param = comm.CreateParameter();
param.ParameterName = "@InvoiceID";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@Po_ID";
param.Value = PoID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PONo";
param.Value = PONo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@PODate";
param.Value = PODate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@InvoiceNo";
param.Value = InvoiceNo;
param.DbType = DbType.String;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@invoiceDate";
param.Value = InvoiceDate;
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@totalAmount";
param.Value = totalAmount;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@EnteredBy";
param.Value = EnterBy;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// result will represent the number of changed rows
int result = -1;
try
{
// execute the stored procedure
result = DBLayer.ExecuteNonQuery(comm);
}
catch
{
// any errors are logged in DataAccess
}
// result will be 1 in case of success
invoiceID = Int32.Parse(comm.Parameters["@InvoiceID"].Value.ToString());
// result will be 1 in case of success
return(result != -1);
}
public override void BindGuid(DbParameter parameter, object value)
{
BindString(parameter, value == null ? DBNull.Value : (object)SqlHelper.GuidToString((Guid)value));
}
private DataTable ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
{
DataTable metaDataCollectionsTable = _metaDataCollectionsDataSet.Tables[DbMetaDataCollectionNames.MetaDataCollections];
DataColumn populationStringColumn = metaDataCollectionsTable.Columns[_populationString];
DataColumn numberOfRestrictionsColumn = metaDataCollectionsTable.Columns[_numberOfRestrictions];
DataColumn collectionNameColumn = metaDataCollectionsTable.Columns[_collectionName];
//DataColumn restrictionNameColumn = metaDataCollectionsTable.Columns[_restrictionName];
DataTable resultTable = null;
DbCommand command = null;
DataTable schemaTable = null;
Debug.Assert(requestedCollectionRow != null);
String sqlCommand = requestedCollectionRow[populationStringColumn, DataRowVersion.Current] as string;
int numberOfRestrictions = (int)requestedCollectionRow[numberOfRestrictionsColumn, DataRowVersion.Current];
String collectionName = requestedCollectionRow[collectionNameColumn, DataRowVersion.Current] as string;
if ((restrictions != null) && (restrictions.Length > numberOfRestrictions))
{
throw ADP.TooManyRestrictions(collectionName);
}
command = connection.CreateCommand();
command.CommandText = sqlCommand;
command.CommandTimeout = System.Math.Max(command.CommandTimeout, 180);
for (int i = 0; i < numberOfRestrictions; i++)
{
DbParameter restrictionParameter = command.CreateParameter();
if ((restrictions != null) && (restrictions.Length > i) && (restrictions[i] != null))
{
restrictionParameter.Value = restrictions[i];
}
else
{
// This is where we have to assign null to the value of the parameter.
restrictionParameter.Value = DBNull.Value;
}
restrictionParameter.ParameterName = GetParameterName(collectionName, i + 1);
restrictionParameter.Direction = ParameterDirection.Input;
command.Parameters.Add(restrictionParameter);
}
DbDataReader reader = null;
try
{
try
{
reader = command.ExecuteReader();
}
catch (Exception e)
{
if (!ADP.IsCatchableExceptionType(e))
{
throw;
}
throw ADP.QueryFailed(collectionName, e);
}
// TODO: Consider using the DataAdapter.Fill
// Build a DataTable from the reader
resultTable = new DataTable(collectionName);
resultTable.Locale = CultureInfo.InvariantCulture;
schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
resultTable.Columns.Add(row["ColumnName"] as string, (Type)row["DataType"] as Type);
}
object[] values = new object[resultTable.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
resultTable.Rows.Add(values);
}
}
finally
{
if (reader != null)
{
reader.Dispose();
reader = null;
}
}
return(resultTable);
}
public int AddParameter(DbParameter[] parameter)
{
int i = 1;
foreach (DbParameter p in parameter)
i *= objCommand.Parameters.Add(p);
return i;
}
override protected void SetParameter(int index, DbParameter value)
{
OnChange();
RangeCheck(index);
Replace(index, value);
}
