public static void DeriveParameters( MySqlCommand command ) {
if( !command.Connection.driver.Version.isAtLeast( 5, 0, 0 ) ) {
throw new MySqlException( "DeriveParameters is not supported on MySQL versions prior to 5.0" );
}
string commandText = command.CommandText;
if( commandText.IndexOf( "." ) == -1 ) {
commandText = command.Connection.Database + "." + commandText;
}
DataSet procedure = command.Connection.ProcedureCache.GetProcedure( command.Connection, commandText );
DataTable table = procedure.Tables[ "Procedure Parameters" ];
DataTable table2 = procedure.Tables[ "Procedures" ];
command.Parameters.Clear();
foreach( DataRow row in table.Rows ) {
MySqlParameter parameter = new MySqlParameter();
parameter.ParameterName = string.Format( "@{0}", row[ "PARAMETER_NAME" ] );
parameter.Direction = GetDirection( row );
bool unsigned = StoredProcedure.GetFlags( row[ "DTD_IDENTIFIER" ].ToString() ).IndexOf( "UNSIGNED" ) != -1;
bool realAsFloat = table2.Rows[ 0 ][ "SQL_MODE" ].ToString().IndexOf( "REAL_AS_FLOAT" ) != -1;
parameter.MySqlDbType = MetaData.NameToType( row[ "DATA_TYPE" ].ToString(), unsigned, realAsFloat, command.Connection );
if( !row[ "CHARACTER_MAXIMUM_LENGTH" ].Equals( DBNull.Value ) ) {
parameter.Size = (int)row[ "CHARACTER_MAXIMUM_LENGTH" ];
}
if( !row[ "NUMERIC_PRECISION" ].Equals( DBNull.Value ) ) {
parameter.Precision = (byte)row[ "NUMERIC_PRECISION" ];
}
if( !row[ "NUMERIC_SCALE" ].Equals( DBNull.Value ) ) {
parameter.Scale = (byte)( (int)row[ "NUMERIC_SCALE" ] );
}
command.Parameters.Add( parameter );
}
}
public bool ExecuteNonQuery(string myExecuteQuery, MySqlParameter[] mySqlParameters)
{
MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, OpenConnection());
myCommand.Parameters.AddRange(mySqlParameters);
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
return true;
}
public MySqlDataReader ExecuteReader(string query, MySqlParameter[] mySqlParameters)
{
{
string mySelectQuery = query;
MySqlCommand myCommand = new MySqlCommand();
myCommand.Connection = OpenConnection();
myCommand.CommandText = query;
myCommand.Parameters.AddRange(mySqlParameters);
MySqlDataReader myReader = myCommand.ExecuteReader();
return myReader;
}
}
public void OutputParameters()
{
if (st.Version < new Version(5, 0)) return;
// we don't want to run this test under no access
string connInfo = st.GetConnectionInfo();
if (connInfo.IndexOf("use procedure bodies=false") != -1) return;
if (st.conn.connectionState != ConnectionState.Open)
st.conn.Open();
// create our procedure
st.execSQL("CREATE PROCEDURE spTest(out value VARCHAR(350), OUT intVal INT, " +
"OUT dateVal TIMESTAMP, OUT floatVal FLOAT, OUT noTypeVarChar VARCHAR(20), " +
"OUT noTypeInt INT) " +
"BEGIN SET value='42'; SET intVal=33; SET dateVal='2004-06-05 07:58:09'; " +
"SET floatVal = 1.2; SET noTypeVarChar='test'; SET noTypeInt=66; END");
// we use rootConn here since we are using parameters
MySqlCommand cmd = new MySqlCommand("spTest", st.conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("?value", MySqlDbType.VarChar));
cmd.Parameters.Add(new MySqlParameter("?intVal", MySqlDbType.Int32));
cmd.Parameters.Add(new MySqlParameter("?dateVal", MySqlDbType.DateTime));
cmd.Parameters.Add(new MySqlParameter("?floatVal", MySqlDbType.Float));
MySqlParameter vcP = new MySqlParameter();
vcP.ParameterName = "?noTypeVarChar";
vcP.Direction = ParameterDirection.Output;
cmd.Parameters.Add(vcP);
MySqlParameter vcI = new MySqlParameter();
vcI.ParameterName = "?noTypeInt";
vcI.Direction = ParameterDirection.Output;
cmd.Parameters.Add(vcI);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters[2].Direction = ParameterDirection.Output;
cmd.Parameters[3].Direction = ParameterDirection.Output;
if (prepare) cmd.Prepare();
int rowsAffected = cmd.ExecuteNonQuery();
//TODO fix this
//Assert.Equal(0, rowsAffected);
Assert.Equal("42", cmd.Parameters[0].Value);
Assert.Equal(33, cmd.Parameters[1].Value);
Assert.Equal(new DateTime(2004, 6, 5, 7, 58, 9),
Convert.ToDateTime(cmd.Parameters[2].Value));
Assert.Equal((decimal)1.2, (decimal)(float)cmd.Parameters[3].Value);
Assert.Equal("test", cmd.Parameters[4].Value);
Assert.Equal(66, cmd.Parameters[5].Value);
}
public void AllowUnnamedParameters()
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES (?id, ?name)", conn);
MySqlParameter p = new MySqlParameter();
p.ParameterName = "?id";
p.Value = 1;
cmd.Parameters.Add(p);
p = new MySqlParameter();
p.ParameterName = "?name";
p.Value = "test";
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT id FROM Test";
Assert.AreEqual(1, cmd.ExecuteScalar());
cmd.CommandText = "SELECT name FROM Test";
Assert.AreEqual("test", cmd.ExecuteScalar());
}
/// <summary>
///
/// </summary>
/// <param name="ParameterName"></param>
/// <param name="DataType"></param>
/// <param name="Value"></param>
/// <param name="Direction"></param>
/// <returns></returns>
internal static MySqlParameter CreateInputParameter(string ParameterName,
MySqlType DataType, object Value, ParameterDirection Direction)
{
MySqlParameter param = new MySqlParameter(ParameterName, DataType);
param.Direction = Direction;
//CStr only for strings
if (DataType == MySqlType.VarChar || DataType == MySqlType.Text)
{
if (Value != null)
{
string strTempStringVal = (string)Value;
if (strTempStringVal.Trim() == "")
param.MySqlValue = null;
else
param.MySqlValue = strTempStringVal.Trim();
}
}
else if (DataType == MySqlType.Char)
{
if (Value != null)
{
Char chrTempCharVal = (Char)Value;
if (chrTempCharVal == ' ')
param.MySqlValue = null;
else
param.MySqlValue = chrTempCharVal;
}
}
else
param.MySqlValue = Value;
return param;
}
public static List <EventsDetail> GetEventDetails(string grpID, string eventID, string groupProfileID)
{
string repeatDateTime = "";
try
{
MySqlParameter[] param = new MySqlParameter[3];
param[0] = new MySqlParameter("@grpID", MySqlDbType.Int16);
param[1] = new MySqlParameter("@eventID", MySqlDbType.Int16);
param[2] = new MySqlParameter("@groupProfileID", MySqlDbType.Int16);
param[0].Value = grpID;
param[1].Value = eventID;
param[2].Value = groupProfileID;
DataSet result = MySqlHelper.ExecuteDataset(GlobalVar.strAppConn, CommandType.StoredProcedure, "V4_USPGetEventListDetails", param);
DataTable dtEvent = result.Tables[0];
DataTable dtRepeatEvent = result.Tables[1];
DataTable dtQuestions = result.Tables[2];
List <EventsDetail> Eventdetail = new List <EventsDetail>();
if (dtEvent.Rows.Count > 0)
{
Eventdetail = GlobalFuns.DataTableToList <EventsDetail>(dtEvent);
if (!string.IsNullOrEmpty(Eventdetail[0].eventImg))
{
string event_Image = Eventdetail[0].eventImg.ToString();
// string path = HttpContext.Current.Server.MapPath("~/Documents/Events/Group" + grpID + "/thumb/");
string path = ConfigurationManager.AppSettings["imgPath"] + "Documents/Event/Group" + grpID + "/";
Eventdetail[0].eventImg = path + event_Image;
}
if (dtRepeatEvent.Rows.Count > 0)
{
for (int i = 0; i < dtRepeatEvent.Rows.Count; i++)
{
repeatDateTime += dtRepeatEvent.Rows[i]["eventDate"].ToString() + ",";
}
repeatDateTime = repeatDateTime.TrimEnd(',');
}
Eventdetail[0].repeatDateTime = repeatDateTime;
//Eventdetail[0].repeatEventResult = new List<object>();
//Eventdetail[0].questionArray = new List<object>();
//if (dtRepeatEvent.Rows.Count > 0)
//{
// List<RepeatEvent> RepeatEvent = GlobalFuns.DataTableToList<RepeatEvent>(dtRepeatEvent);
// for (int i = 0; i < RepeatEvent.Count; i++)
// {
// Eventdetail[0].repeatEventResult.Add(new { RepeatEvent = (object)RepeatEvent[i] });
// }
//}
if (dtQuestions.Rows.Count == 1)
{
Eventdetail[0].questionId = dtQuestions.Rows[0]["questionId"].ToString();
Eventdetail[0].questionText = dtQuestions.Rows[0]["questionText"].ToString();
Eventdetail[0].questionType = dtQuestions.Rows[0]["questionType"].ToString();
Eventdetail[0].option1 = dtQuestions.Rows[0]["option1"].ToString();
Eventdetail[0].option2 = dtQuestions.Rows[0]["option2"].ToString();
}
else
{
Eventdetail[0].questionId = "";
Eventdetail[0].questionText = "";
Eventdetail[0].questionType = "";
Eventdetail[0].option1 = "";
Eventdetail[0].option2 = "";
}
}
return(Eventdetail);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
MySqlParameter myParameter = new MySqlParameter("?fs", MySqlDbType.Blob);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
MySqlParameter myParameter = new MySqlParameter("?content", MySqlDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public bool Update(RecordsMedicineCnModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("update ARCHIVE_MEDICINE_CN set ");
builder.Append("RecordID=@RecordID,");
builder.Append("IDCardNo=@IDCardNo,");
builder.Append("CustomerID=@CustomerID,");
builder.Append("PhysicalID=@PhysicalID,");
builder.Append("Energy=@Energy,");
builder.Append("Tired=@Tired,");
builder.Append("Breath=@Breath,");
builder.Append("Voice=@Voice,");
builder.Append("Emotion=@Emotion,");
builder.Append("Spirit=@Spirit,");
builder.Append("Alone=@Alone,");
builder.Append("Fear=@Fear,");
builder.Append("Weight=@Weight,");
builder.Append("Eye=@Eye,");
builder.Append("FootHand=@FootHand,");
builder.Append("Stomach=@Stomach,");
builder.Append("Cold=@Cold,");
builder.Append("Influenza=@Influenza,");
builder.Append("Nasal=@Nasal,");
builder.Append("Snore=@Snore,");
builder.Append("Allergy=@Allergy,");
builder.Append("Urticaria=@Urticaria,");
builder.Append("Skin=@Skin,");
builder.Append("Scratch=@Scratch,");
builder.Append("Mouth=@Mouth,");
builder.Append("Arms=@Arms,");
builder.Append("Greasy=@Greasy,");
builder.Append("Spot=@Spot,");
builder.Append("Eczema=@Eczema,");
builder.Append("Thirsty=@Thirsty,");
builder.Append("Smell=@Smell,");
builder.Append("Abdomen=@Abdomen,");
builder.Append("Coolfood=@Coolfood,");
builder.Append("Defecate=@Defecate,");
builder.Append("Defecatedry=@Defecatedry,");
builder.Append("Tongue=@Tongue,");
builder.Append("Vein=@Vein,");
builder.Append("CreatedBy=@CreatedBy,");
builder.Append("CreatedDate=@CreatedDate,");
builder.Append("LastUpdateBy=@LastUpdateBy,");
builder.Append("LastUpdateDate=@LastUpdateDate,");
builder.Append("FollowUpDoctor=@FollowUpDoctor,");
builder.Append("RecordDate=@RecordDate,");
builder.Append("IsDel=@IsDel");
builder.Append(" where ID=@ID");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@CustomerID", MySqlDbType.String, 17),
new MySqlParameter("@PhysicalID", MySqlDbType.String, 17),
new MySqlParameter("@Energy", MySqlDbType.Decimal),
new MySqlParameter("@Tired", MySqlDbType.Decimal),
new MySqlParameter("@Breath", MySqlDbType.Decimal),
new MySqlParameter("@Voice", MySqlDbType.Decimal),
new MySqlParameter("@Emotion", MySqlDbType.Decimal),
new MySqlParameter("@Spirit", MySqlDbType.Decimal),
new MySqlParameter("@Alone", MySqlDbType.Decimal),
new MySqlParameter("@Fear", MySqlDbType.Decimal),
new MySqlParameter("@Weight", MySqlDbType.Decimal),
new MySqlParameter("@Eye", MySqlDbType.Decimal),
new MySqlParameter("@FootHand", MySqlDbType.Decimal),
new MySqlParameter("@Stomach", MySqlDbType.Decimal),
new MySqlParameter("@Cold", MySqlDbType.Decimal),
new MySqlParameter("@Influenza", MySqlDbType.Decimal),
new MySqlParameter("@Nasal", MySqlDbType.Decimal),
new MySqlParameter("@Snore", MySqlDbType.Decimal),
new MySqlParameter("@Allergy", MySqlDbType.Decimal),
new MySqlParameter("@Urticaria", MySqlDbType.Decimal),
new MySqlParameter("@Skin", MySqlDbType.Decimal),
new MySqlParameter("@Scratch", MySqlDbType.Decimal),
new MySqlParameter("@Mouth", MySqlDbType.Decimal),
new MySqlParameter("@Arms", MySqlDbType.Decimal),
new MySqlParameter("@Greasy", MySqlDbType.Decimal),
new MySqlParameter("@Spot", MySqlDbType.Decimal),
new MySqlParameter("@Eczema", MySqlDbType.Decimal),
new MySqlParameter("@Thirsty", MySqlDbType.Decimal),
new MySqlParameter("@Smell", MySqlDbType.Decimal),
new MySqlParameter("@Abdomen", MySqlDbType.Decimal),
new MySqlParameter("@Coolfood", MySqlDbType.Decimal),
new MySqlParameter("@Defecate", MySqlDbType.Decimal),
new MySqlParameter("@Defecatedry", MySqlDbType.Decimal),
new MySqlParameter("@Tongue", MySqlDbType.Decimal),
new MySqlParameter("@Vein", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.Decimal),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.Decimal),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@FollowUpDoctor", MySqlDbType.String, 20),
new MySqlParameter("@RecordDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@ID", MySqlDbType.Int32, 8)
};
cmdParms[0].Value = model.RecordID;
cmdParms[1].Value = model.IDCardNo;
cmdParms[2].Value = model.CustomerID;
cmdParms[3].Value = model.PhysicalID;
cmdParms[4].Value = model.Energy;
cmdParms[5].Value = model.Tired;
cmdParms[6].Value = model.Breath;
cmdParms[7].Value = model.Voice;
cmdParms[8].Value = model.Emotion;
cmdParms[9].Value = model.Spirit;
cmdParms[10].Value = model.Alone;
cmdParms[11].Value = model.Fear;
cmdParms[12].Value = model.Weight;
cmdParms[13].Value = model.Eye;
cmdParms[14].Value = model.FootHand;
cmdParms[15].Value = model.Stomach;
cmdParms[16].Value = model.Cold;
cmdParms[17].Value = model.Influenza;
cmdParms[18].Value = model.Nasal;
cmdParms[19].Value = model.Snore;
cmdParms[20].Value = model.Allergy;
cmdParms[21].Value = model.Urticaria;
cmdParms[22].Value = model.Skin;
cmdParms[23].Value = model.Scratch;
cmdParms[24].Value = model.Mouth;
cmdParms[25].Value = model.Arms;
cmdParms[26].Value = model.Greasy;
cmdParms[27].Value = model.Spot;
cmdParms[28].Value = model.Eczema;
cmdParms[29].Value = model.Thirsty;
cmdParms[30].Value = model.Smell;
cmdParms[31].Value = model.Abdomen;
cmdParms[32].Value = model.Coolfood;
cmdParms[33].Value = model.Defecate;
cmdParms[34].Value = model.Defecatedry;
cmdParms[35].Value = model.Tongue;
cmdParms[36].Value = model.Vein;
cmdParms[37].Value = model.CreatedBy;
cmdParms[38].Value = model.CreatedDate;
cmdParms[39].Value = model.LastUpdateBy;
cmdParms[40].Value = model.LastUpdateDate;
cmdParms[41].Value = model.FollowUpDoctor;
cmdParms[42].Value = model.RecordDate;
cmdParms[43].Value = model.IsDel;
cmdParms[44].Value = model.ID;
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
/// <summary>
/// Updates a row in the mp_ContentMeta table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="name"> name </param>
/// <param name="scheme"> scheme </param>
/// <param name="langCode"> langCode </param>
/// <param name="dir"> dir </param>
/// <param name="metaContent"> metaContent </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
string name,
string scheme,
string langCode,
string dir,
string metaContent,
int sortRank,
DateTime lastModUtc,
Guid lastModBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_ContentMeta ");
sqlCommand.Append("SET ");
sqlCommand.Append("Name = ?Name, ");
sqlCommand.Append("Scheme = ?Scheme, ");
sqlCommand.Append("LangCode = ?LangCode, ");
sqlCommand.Append("Dir = ?Dir, ");
sqlCommand.Append("MetaContent = ?MetaContent, ");
sqlCommand.Append("SortRank = ?SortRank, ");
sqlCommand.Append("LastModUtc = ?LastModUtc, ");
sqlCommand.Append("LastModBy = ?LastModBy ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[9];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = name;
arParams[2] = new MySqlParameter("?Scheme", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = scheme;
arParams[3] = new MySqlParameter("?LangCode", MySqlDbType.VarChar, 10);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = langCode;
arParams[4] = new MySqlParameter("?Dir", MySqlDbType.VarChar, 3);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = dir;
arParams[5] = new MySqlParameter("?MetaContent", MySqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = metaContent;
arParams[6] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = sortRank;
arParams[7] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = lastModUtc;
arParams[8] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Metodo que atribui paramentros no Cmd(parametro) passado, conforme o Banco(FireBird, SQLServer, MySQL).
/// </summary>
/// <param name="Cmd">DBCommand que irá receber os paramentros</param>
/// <param name="ParamsList">Lista dos paramentros.</param>
/// <returns>Retorna um int, indicando a quantidade de parametros adicionados ao Cmd</returns>
private int SetParams(IDbCommand Cmd, SqlParamsList ParamsList)
{
int iReturn = 0;
if (ParamsList == null)
{
return(iReturn);
}
char cDef = '@';
char cNew = ' ';
switch (this.Tipobanco)
{
case TipoBanco.SqlServer:
case TipoBanco.Firebird:
cNew = '@';
break;
case TipoBanco.MySql5:
cNew = '?';
break;
}
Cmd.CommandText = Cmd.CommandText.Replace(cDef, cNew);
Cmd.Parameters.Clear();
foreach (Fields Param in ParamsList)
{
switch (this.Tipobanco)
{
case TipoBanco.SqlServer:
SqlParameter ParamSQL = new SqlParameter(Param.Nome, Param.Valor);
Cmd.Parameters.Add(ParamSQL);
break;
case TipoBanco.MySql5:
MySqlParameter ParamMy = new MySqlParameter(cNew + Param.Nome, Param.Valor);
if (Param.Valor is char)
{
ParamMy.DbType = DbType.String;
}
Cmd.Parameters.Add(ParamMy);
break;
case TipoBanco.Firebird:
if (Param.Nome.Substring(0, 1) != "@")
{
Param.Nome = "@" + Param.Nome;
}
FbParameter ParamFb = new FbParameter();
ParamFb.ParameterName = Param.Nome;
ParamFb.Value = Param.Valor;
Cmd.Parameters.Add(ParamFb);
break;
}
iReturn++;
}
return(iReturn);
}
private void RegistraAdmin(String psCorreo, String psNombre, String psContra, String psPregunta, String psRespuesta, String psFechaNac, String psTel)
{
if (MessageBox.Show("Desea agregar el usuario?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
MySqlConnection conexion = new MySqlConnection(); //objeto de conexion
string Cadenaconexion; //variable para recibir los valores
//cadena de conexion que va a recibir
Cadenaconexion = "Server=localhost;User id=root; Database=proyecto;Password=;"; //preparar la ruta
conexion.ConnectionString = Cadenaconexion; //a la cadena de conexion se le agrega la conexion
String sInsert = "INSERT INTO admin(correo,nombre,contrasena,pregunta,respuesta,fecha_nac,telefono) values " +
"(@correo,@nombre,aes_encrypt(@contrasena,@respuesta),aes_encrypt(@pregunta,@correo),aes_encrypt(@respuesta,'root'),@fechaNac,@Tel)";
// String sInsert = "INSERT INTO admin(correo,nombre,contrasena,pregunta,respuesta,fecha_nac,telefono) values "+
// "(@correo,@nombre,aes_encrypt(@contrasena,@respuesta),@pregunta,aes_encrypt(@respuesta,'s3cret'),@fechaNac,@Tel)";
MySqlCommand comando = new MySqlCommand(sInsert); //query comando
comando.Connection = conexion;
//creacion del objeto
MySqlParameter parametro1 = new MySqlParameter(); //dealaracion del obejto
parametro1.ParameterName = "@correo"; //asigancion
parametro1.Value = psCorreo;
//creacion del objeto
MySqlParameter parametro2 = new MySqlParameter(); //dealaracion del obejto
parametro2.ParameterName = "@nombre"; //asigancion
parametro2.Value = psNombre;
MySqlParameter parametro3 = new MySqlParameter(); //dealaracion del obejto
parametro3.ParameterName = "@contrasena"; //asigancion
parametro3.Value = psContra;
MySqlParameter parametro4 = new MySqlParameter(); //dealaracion del obejto
parametro4.ParameterName = "@pregunta"; //asigancion
parametro4.Value = psPregunta;
MySqlParameter parametro5 = new MySqlParameter(); //dealaracion del obejto
parametro5.ParameterName = "@respuesta"; //asigancion
parametro5.Value = psRespuesta;
MySqlParameter parametro6 = new MySqlParameter(); //dealaracion del obejto
parametro6.ParameterName = "@fechaNac"; //asigancion
parametro6.Value = psFechaNac;
MySqlParameter parametro7 = new MySqlParameter(); //dealaracion del obejto
parametro7.ParameterName = "@Tel"; //asigancion
parametro7.Value = psTel;
comando.Parameters.Add(parametro1);
comando.Parameters.Add(parametro2);
comando.Parameters.Add(parametro3);
comando.Parameters.Add(parametro4);
comando.Parameters.Add(parametro5);
comando.Parameters.Add(parametro6);
comando.Parameters.Add(parametro7);
try
{
conexion.Open(); //abrir conexion
int iRegAfec = comando.ExecuteNonQuery(); //ejecutar query
if (iRegAfec == 1)
{
MessageBox.Show("Usuario agregado correctamente", "Confirmacion", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
limpiarCampos();
}
else
{
MessageBox.Show("El usuario no fue agregado", "Confirmacion", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show("Error de tipo: " + ex);
}
conexion.Close();
}
}
public bool Update(RecordsChdStrokeReportModel model)
{
StringBuilder sb = new StringBuilder();
sb.Append("update ARCHIVE_CHD_STROKE_REPORT set ");
sb.Append("PatientNo=@PatientNo,AdmissionNo=@AdmissionNo,CardNo=@CardNo,IDCardNo=@IDCardNo,ICD10Code=@ICD10Code,AddDistrict=@AddDistrict,HouesAddDistrict=@HouesAddDistrict, ");
sb.Append("AddTown=@AddTown,HouesAddTown=@HouesAddTown,AddVillage=@AddVillage,HouseAddVillage=@HouseAddVillage,AddNum=@AddNum,HouseAddNum=@HouseAddNum, ");
sb.Append("AcuteMI=@AcuteMI,SAH=@SAH,Diagnosis=@Diagnosis,");
sb.Append("DiseaseTime=@DiseaseTime,DiagnosisTime=@DiagnosisTime,FirstOnset=@FirstOnset,");
sb.Append("ConfirmedUnit=@ConfirmedUnit,CardUnit=@CardUnit,CardDoctor=@CardDoctor,CardDate=@CardDate,DeathDate=@DeathDate,DeathReason=@DeathReason,DeathCode=@DeathCode,OutKey=@OutKey ");
sb.Append(" where ID=@ID");
MySqlParameter[] cmdParms = new MySqlParameter[]
{
new MySqlParameter("@PatientNo", MySqlDbType.String, 20),
new MySqlParameter("@AdmissionNo", MySqlDbType.String, 20),
new MySqlParameter("@CardNo", MySqlDbType.String, 20),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 18),
new MySqlParameter("@ICD10Code", MySqlDbType.String),
new MySqlParameter("@AddDistrict", MySqlDbType.String),
new MySqlParameter("@HouesAddDistrict", MySqlDbType.String),
new MySqlParameter("@AddTown", MySqlDbType.String),
new MySqlParameter("@HouesAddTown", MySqlDbType.String),
new MySqlParameter("@AddVillage", MySqlDbType.String),
new MySqlParameter("@HouseAddVillage", MySqlDbType.String),
new MySqlParameter("@AddNum", MySqlDbType.String),
new MySqlParameter("@HouseAddNum", MySqlDbType.String),
new MySqlParameter("@AcuteMI", MySqlDbType.String),
new MySqlParameter("@SAH", MySqlDbType.String),
new MySqlParameter("@Diagnosis", MySqlDbType.String),
new MySqlParameter("@DiseaseTime", MySqlDbType.Date),
new MySqlParameter("@DiagnosisTime", MySqlDbType.Date),
new MySqlParameter("@FirstOnset", MySqlDbType.String),
new MySqlParameter("@ConfirmedUnit", MySqlDbType.String),
new MySqlParameter("@CardUnit", MySqlDbType.String),
new MySqlParameter("@CardDoctor", MySqlDbType.String),
new MySqlParameter("@CardDate", MySqlDbType.Date),
new MySqlParameter("@DeathDate", MySqlDbType.Date),
new MySqlParameter("@DeathReason", MySqlDbType.String),
new MySqlParameter("@DeathCode", MySqlDbType.String),
new MySqlParameter("@OutKey", MySqlDbType.Int32, 4),
new MySqlParameter("@ID", MySqlDbType.Int32)
};
cmdParms[0].Value = model.PatientNo;
cmdParms[1].Value = model.AdmissionNo;
cmdParms[2].Value = model.CardNo;
cmdParms[3].Value = model.IDCardNo;
cmdParms[4].Value = model.ICD10Code;
cmdParms[5].Value = model.AddDistrict;
cmdParms[6].Value = model.HouesAddDistrict;
cmdParms[7].Value = model.AddTown;
cmdParms[8].Value = model.HouesAddTown;
cmdParms[9].Value = model.AddVillage;
cmdParms[10].Value = model.HouseAddVillage;
cmdParms[11].Value = model.AddNum;
cmdParms[12].Value = model.HouseAddNum;
cmdParms[13].Value = model.AcuteMI;
cmdParms[14].Value = model.SAH;
cmdParms[15].Value = model.Diagnosis;
cmdParms[16].Value = model.DiseaseTime;
cmdParms[17].Value = model.DiagnosisTime;
cmdParms[18].Value = model.FirstOnset;
cmdParms[19].Value = model.ConfirmedUnit;
cmdParms[20].Value = model.CardUnit;
cmdParms[21].Value = model.CardDoctor;
cmdParms[22].Value = model.CardDate;
cmdParms[23].Value = model.DeathDate;
cmdParms[24].Value = model.DeathReason;
cmdParms[25].Value = model.DeathCode;
cmdParms[26].Value = model.OutKey;
cmdParms[27].Value = model.ID;
return(MySQLHelper.ExecuteSql(sb.ToString(), cmdParms) > 0);
}
public int Add(RecordsChdStrokeReportModel model)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into ARCHIVE_CHD_STROKE_REPORT(");
sb.Append("PatientNo,AdmissionNo,CardNo,IDCardNo,ICD10Code,Age,Phone,AddDistrict,HouesAddDistrict,AddTown,HouesAddTown,AddVillage,HouseAddVillage,AddNum,HouseAddNum,");
sb.Append("AcuteMI,SAH,Diagnosis,DiseaseTime,DiagnosisTime,FirstOnset,ConfirmedUnit,CardUnit,CardDoctor,CardDate,DeathDate,DeathReason,DeathCode,OutKey )");
sb.Append("values(");
sb.Append("@PatientNo,@AdmissionNo,@CardNo,@IDCardNo,@ICD10Code,@Age,@Phone,@AddDistrict,@HouesAddDistrict,@AddTown,@HouesAddTown,@AddVillage,@HouseAddVillage,@AddNum,@HouseAddNum,");
sb.Append("@AcuteMI,@SAH,@Diagnosis,@DiseaseTime,@DiagnosisTime,@FirstOnset,@ConfirmedUnit,@CardUnit,@CardDoctor,@CardDate,@DeathDate,@DeathReason,@DeathCode,@OutKey )");
sb.Append(";select @@IDENTITY");
MySqlParameter[] cmdParms = new MySqlParameter[]
{
new MySqlParameter("@PatientNo", MySqlDbType.String, 20),
new MySqlParameter("@AdmissionNo", MySqlDbType.String, 20),
new MySqlParameter("@CardNo", MySqlDbType.String, 20),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 18),
new MySqlParameter("@ICD10Code", MySqlDbType.String),
new MySqlParameter("@Age", MySqlDbType.String),
new MySqlParameter("@Phone", MySqlDbType.String),
new MySqlParameter("@AddDistrict", MySqlDbType.String),
new MySqlParameter("@HouesAddDistrict", MySqlDbType.String),
new MySqlParameter("@AddTown", MySqlDbType.String),
new MySqlParameter("@HouesAddTown", MySqlDbType.String),
new MySqlParameter("@AddVillage", MySqlDbType.String),
new MySqlParameter("@HouseAddVillage", MySqlDbType.String),
new MySqlParameter("@AddNum", MySqlDbType.String),
new MySqlParameter("@HouseAddNum", MySqlDbType.String),
new MySqlParameter("@AcuteMI", MySqlDbType.String),
new MySqlParameter("@SAH", MySqlDbType.String),
new MySqlParameter("@Diagnosis", MySqlDbType.String),
new MySqlParameter("@DiseaseTime", MySqlDbType.Date),
new MySqlParameter("@DiagnosisTime", MySqlDbType.Date),
new MySqlParameter("@FirstOnset", MySqlDbType.String),
new MySqlParameter("@ConfirmedUnit", MySqlDbType.String),
new MySqlParameter("@CardUnit", MySqlDbType.String),
new MySqlParameter("@CardDoctor", MySqlDbType.String),
new MySqlParameter("@CardDate", MySqlDbType.Date),
new MySqlParameter("@DeathDate", MySqlDbType.Date),
new MySqlParameter("@DeathReason", MySqlDbType.String),
new MySqlParameter("@DeathCode", MySqlDbType.String),
new MySqlParameter("@OutKey", MySqlDbType.Int32, 4)
};
cmdParms[0].Value = model.PatientNo;
cmdParms[1].Value = model.AdmissionNo;
cmdParms[2].Value = model.CardNo;
cmdParms[3].Value = model.IDCardNo;
cmdParms[4].Value = model.ICD10Code;
cmdParms[5].Value = model.Age;
cmdParms[6].Value = model.Phone;
cmdParms[7].Value = model.AddDistrict;
cmdParms[8].Value = model.HouesAddDistrict;
cmdParms[9].Value = model.AddTown;
cmdParms[10].Value = model.HouesAddTown;
cmdParms[11].Value = model.AddVillage;
cmdParms[12].Value = model.HouseAddVillage;
cmdParms[13].Value = model.AddNum;
cmdParms[14].Value = model.HouseAddNum;
cmdParms[15].Value = model.AcuteMI;
cmdParms[16].Value = model.SAH;
cmdParms[17].Value = model.Diagnosis;
cmdParms[18].Value = model.DiseaseTime;
cmdParms[19].Value = model.DiagnosisTime;
cmdParms[20].Value = model.FirstOnset;
cmdParms[21].Value = model.ConfirmedUnit;
cmdParms[22].Value = model.CardUnit;
cmdParms[23].Value = model.CardDoctor;
cmdParms[24].Value = model.CardDate;
cmdParms[25].Value = model.DeathDate;
cmdParms[26].Value = model.DeathReason;
cmdParms[27].Value = model.DeathCode;
cmdParms[28].Value = model.OutKey;
object single = MySQLHelper.GetSingle(sb.ToString(), cmdParms);
if (single == null)
{
return(0);
}
return(Convert.ToInt32(single));
}
private void InitAdapter()
{
this._adapter = new MySqlDataAdapter();
DataTableMapping mapping = new DataTableMapping {
SourceTable = "Table",
DataSetTable = "tbl_expences",
ColumnMappings =
{
{
"expences_id",
"expences_id"
},
{
"description",
"description"
},
{
"date",
"date"
},
{
"userId",
"userId"
},
{
"amount",
"amount"
},
{
"name",
"name"
}
}
};
this._adapter.TableMappings.Add(mapping);
this._adapter.DeleteCommand = new MySqlCommand();
this._adapter.DeleteCommand.Connection = this.Connection;
this._adapter.DeleteCommand.CommandText = "DELETE FROM `tbl_expences` WHERE ((`expences_id` = @p1) AND (`description` = @p2) AND (`date` = @p3) AND (`userId` = @p4) AND (`amount` = @p5) AND (`name` = @p6))";
this._adapter.DeleteCommand.CommandType = CommandType.Text;
MySqlParameter parameter = new MySqlParameter {
ParameterName = "@p1",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "expences_id",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p2",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "description",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p3",
DbType = DbType.Date,
MySqlDbType = MySqlDbType.Date,
IsNullable = true,
SourceColumn = "date",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p4",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "userId",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p5",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "amount",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p6",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "name",
SourceVersion = DataRowVersion.Original
};
this._adapter.DeleteCommand.Parameters.Add(parameter);
this._adapter.InsertCommand = new MySqlCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO `tbl_expences` (`description`, `date`, `userId`, `amount`, `name`) VALUES (@p1, @p2, @p3, @p4, @p5)";
this._adapter.InsertCommand.CommandType = CommandType.Text;
parameter = new MySqlParameter {
ParameterName = "@p1",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "description"
};
this._adapter.InsertCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p2",
DbType = DbType.Date,
MySqlDbType = MySqlDbType.Date,
IsNullable = true,
SourceColumn = "date"
};
this._adapter.InsertCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p3",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "userId"
};
this._adapter.InsertCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p4",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "amount"
};
this._adapter.InsertCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p5",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "name"
};
this._adapter.InsertCommand.Parameters.Add(parameter);
this._adapter.UpdateCommand = new MySqlCommand();
this._adapter.UpdateCommand.Connection = this.Connection;
this._adapter.UpdateCommand.CommandText = "UPDATE `tbl_expences` SET `description` = @p1, `date` = @p2, `userId` = @p3, `amount` = @p4, `name` = @p5 WHERE ((`expences_id` = @p6) AND (`description` = @p7) AND (`date` = @p8) AND (`userId` = @p9) AND (`amount` = @p10) AND (`name` = @p11))";
this._adapter.UpdateCommand.CommandType = CommandType.Text;
parameter = new MySqlParameter {
ParameterName = "@p1",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "description"
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p2",
DbType = DbType.Date,
MySqlDbType = MySqlDbType.Date,
IsNullable = true,
SourceColumn = "date"
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p3",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "userId"
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p4",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "amount"
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p5",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "name"
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p6",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "expences_id",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p7",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "description",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p8",
DbType = DbType.Date,
MySqlDbType = MySqlDbType.Date,
IsNullable = true,
SourceColumn = "date",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p9",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "userId",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p10",
DbType = DbType.UInt32,
MySqlDbType = MySqlDbType.UInt32,
IsNullable = true,
SourceColumn = "amount",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
parameter = new MySqlParameter {
ParameterName = "@p11",
DbType = DbType.String,
MySqlDbType = MySqlDbType.VarChar,
IsNullable = true,
SourceColumn = "name",
SourceVersion = DataRowVersion.Original
};
this._adapter.UpdateCommand.Parameters.Add(parameter);
}
/* ****************************************************************************/
/* Metodo que actualiza la informacion del empleado en la Base de datos
* /* ****************************************************************************/
public int GuardarInfoEmpleado(string id_Empleado)
{
CnMysql Conexion = new CnMysql(Cn);
int res = 0;
try
{
Conexion.AbrirCnMysql();
MySqlCommand cmd = new MySqlCommand(bd2 + ".sp_ActualizaEmpleado", Conexion.ObtenerCnMysql());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@idEmpleado", TxtDoc.Text);
cmd.Parameters.AddWithValue("@nombresEmpleado", TxtNombres.Text);
cmd.Parameters.AddWithValue("@PApellidoEmpleado", TxtPrimerApellido.Text);
cmd.Parameters.AddWithValue("@SApellidoEmpleado", TxtSegundoApellido.Text);
string NombreCompleto = TxtPrimerApellido.Text + " " + TxtSegundoApellido.Text + " " + TxtNombres.Text;
cmd.Parameters.AddWithValue("@nombreCompletoEmpleado", NombreCompleto);
cmd.Parameters.AddWithValue("@SexoEmpleado", TxtSexoEmp.SelectedValue);
cmd.Parameters.AddWithValue("@expDocumento", TxtExp.Text);
cmd.Parameters.AddWithValue("@correo", TxtCorreo.Text);
cmd.Parameters.AddWithValue("@barrio", TxtBarrio.Text);
cmd.Parameters.AddWithValue("@celular", TxtCelular.Text);
cmd.Parameters.AddWithValue("@EPS", TxtEps.Text);
cmd.Parameters.AddWithValue("@AFP", TxtAfp.Text);
cmd.Parameters.AddWithValue("@telefono", TxtTelefono.Text);
cmd.Parameters.AddWithValue("@cesantias", TxtCesantias.Text);
cmd.Parameters.AddWithValue("@estadoCivil", TxtEstCivil.SelectedValue);
cmd.Parameters.AddWithValue("@direccion", TxtDir.Text);
string FechaNacimiento = TxtFechNac.Text.Replace("-", "").Replace("/", "");
cmd.Parameters.AddWithValue("@fechaNacimiento", FechaNacimiento);
cmd.Parameters.AddWithValue("@lugarNacimiento", ddlCiudades.SelectedValue);
if (Session["cambiaContrasena"].ToString() == "True")
{
cmd.Parameters.AddWithValue("@contrasena", TxtPass.Text);
}
else
{
cmd.Parameters.AddWithValue("@contrasena", Session["Contrasena"].ToString());
}
// Crea un parametro de salida para el SP
MySqlParameter outputIdParam = new MySqlParameter("@respuesta", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outputIdParam);
cmd.ExecuteNonQuery();
//Almacena la respuesta de la variable de retorno del SP
res = int.Parse(outputIdParam.Value.ToString());
Session.Remove("Contrasena");
Session.Remove("cambiaContrasena");
return(res);
}
catch (Exception E)
{
MensajeError("Ha ocurrido el siguiente error: " + E.Message + " _Metodo: " + System.Reflection.MethodBase.GetCurrentMethod().Name);
Session.Remove("Contrasena");
Session.Remove("cambiaContrasena");
return(res);
}
finally
{
Conexion.CerrarCnMysql();
}
}
/// <summary>
/// Inserts a row in the mp_ContentMeta table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="name"> name </param>
/// <param name="scheme"> scheme </param>
/// <param name="langCode"> langCode </param>
/// <param name="dir"> dir </param>
/// <param name="metaContent"> metaContent </param>
/// <param name="sortRank"> sortRank </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid contentGuid,
string name,
string scheme,
string langCode,
string dir,
string metaContent,
int sortRank,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ContentMeta (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("ContentGuid, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Scheme, ");
sqlCommand.Append("LangCode, ");
sqlCommand.Append("Dir, ");
sqlCommand.Append("MetaContent, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUtc, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?Guid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?ContentGuid, ");
sqlCommand.Append("?Name, ");
sqlCommand.Append("?Scheme, ");
sqlCommand.Append("?LangCode, ");
sqlCommand.Append("?Dir, ");
sqlCommand.Append("?MetaContent, ");
sqlCommand.Append("?SortRank, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?CreatedBy, ");
sqlCommand.Append("?LastModUtc, ");
sqlCommand.Append("?LastModBy )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[14];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new MySqlParameter("?ContentGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = contentGuid.ToString();
arParams[4] = new MySqlParameter("?Name", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new MySqlParameter("?Scheme", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = scheme;
arParams[6] = new MySqlParameter("?LangCode", MySqlDbType.VarChar, 10);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = langCode;
arParams[7] = new MySqlParameter("?Dir", MySqlDbType.VarChar, 3);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = dir;
arParams[8] = new MySqlParameter("?MetaContent", MySqlDbType.Text);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = metaContent;
arParams[9] = new MySqlParameter("?SortRank", MySqlDbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortRank;
arParams[10] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = createdUtc;
arParams[11] = new MySqlParameter("?CreatedBy", MySqlDbType.VarChar, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdBy.ToString();
arParams[12] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = createdUtc;
arParams[13] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = createdBy.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public void NonInputParametersToCtor()
{
MySqlParameter p = new MySqlParameter("?p1", MySqlDbType.VarChar, 20,
ParameterDirection.InputOutput, true, 0, 0, "id", DataRowVersion.Current, 0);
Assert.AreEqual(ParameterDirection.InputOutput, p.Direction);
MySqlParameter p1 = new MySqlParameter("?p1", MySqlDbType.VarChar, 20,
ParameterDirection.Output, true, 0, 0, "id", DataRowVersion.Current, 0);
Assert.AreEqual(ParameterDirection.Output, p1.Direction);
}
public void NotSpecifyingDataTypeOfReturnValue()
{
if (Version < new Version(5, 0)) return;
execSQL(@"CREATE FUNCTION `TestFunction`()
RETURNS varchar(20)
RETURN ''");
MySqlCommand cmd = new MySqlCommand("TestFunction", conn);
cmd.CommandType = CommandType.StoredProcedure;
MySqlParameter returnParam = new MySqlParameter();
returnParam.ParameterName = "?RetVal_";
returnParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParam);
cmd.ExecuteNonQuery();
}
/// <summary>
/// 根据表,获取一个MySqlParameter数组
/// </summary>
/// <returns>MySqlParameter[]</returns>
public static MySqlParameter[] GetSqlParameters(data_hotkeys data_hotkeysExample)
{
List <MySqlParameter> list_param = new List <MySqlParameter>();
if (data_hotkeysExample.dhdate != new DateTime() && data_hotkeysExample.dhdate != null)
{
list_param.Add(new MySqlParameter("@dhdate", data_hotkeysExample.dhdate.ToString("yyyy-MM-dd")));
}
else
{
list_param.Add(new MySqlParameter("@dhdate", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhtypebag))
{
list_param.Add(new MySqlParameter("@dhtypebag", data_hotkeysExample.dhtypebag));
}
else
{
list_param.Add(new MySqlParameter("@dhtypebag", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhtypekey))
{
list_param.Add(new MySqlParameter("@dhtypekey", data_hotkeysExample.dhtypekey));
}
else
{
list_param.Add(new MySqlParameter("@dhtypekey", DBNull.Value));
}
if (data_hotkeysExample.dhrank != 0)
{
list_param.Add(new MySqlParameter("@dhrank", data_hotkeysExample.dhrank));
}
else
{
list_param.Add(new MySqlParameter("@dhrank", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhkey))
{
list_param.Add(new MySqlParameter("@dhkey", data_hotkeysExample.dhkey));
}
else
{
list_param.Add(new MySqlParameter("@dhkey", DBNull.Value));
}
if (data_hotkeysExample.dhsearchtimes != 0)
{
list_param.Add(new MySqlParameter("@dhsearchtimes", data_hotkeysExample.dhsearchtimes));
}
else
{
list_param.Add(new MySqlParameter("@dhsearchtimes", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhmall))
{
list_param.Add(new MySqlParameter("@dhmall", data_hotkeysExample.dhmall));
}
else
{
list_param.Add(new MySqlParameter("@dhmall", DBNull.Value));
}
if (data_hotkeysExample.dhclickrate != new Double())
{
list_param.Add(new MySqlParameter("@dhclickrate", data_hotkeysExample.dhclickrate));
}
else
{
list_param.Add(new MySqlParameter("@dhclickrate", DBNull.Value));
}
if (data_hotkeysExample.dhclicktimes != 0)
{
list_param.Add(new MySqlParameter("@dhclicktimes", data_hotkeysExample.dhclicktimes));
}
else
{
list_param.Add(new MySqlParameter("@dhclicktimes", DBNull.Value));
}
if (data_hotkeysExample.dhconverrate != new Double())
{
list_param.Add(new MySqlParameter("@dhconverrate", data_hotkeysExample.dhconverrate));
}
else
{
list_param.Add(new MySqlParameter("@dhconverrate", DBNull.Value));
}
if (data_hotkeysExample.dhzhitongche != new Double())
{
list_param.Add(new MySqlParameter("@dhzhitongche", data_hotkeysExample.dhzhitongche));
}
else
{
list_param.Add(new MySqlParameter("@dhzhitongche", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhtypebiaosheng))
{
list_param.Add(new MySqlParameter("@dhtypebiaosheng", data_hotkeysExample.dhtypebiaosheng));
}
else
{
list_param.Add(new MySqlParameter("@dhtypebiaosheng", DBNull.Value));
}
if (data_hotkeysExample.dhxiangguanci != 0)
{
list_param.Add(new MySqlParameter("@dhxiangguanci", data_hotkeysExample.dhxiangguanci));
}
else
{
list_param.Add(new MySqlParameter("@dhxiangguanci", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhremark1))
{
list_param.Add(new MySqlParameter("@dhremark1", data_hotkeysExample.dhremark1));
}
else
{
list_param.Add(new MySqlParameter("@dhremark1", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhremark2))
{
list_param.Add(new MySqlParameter("@dhremark2", data_hotkeysExample.dhremark2));
}
else
{
list_param.Add(new MySqlParameter("@dhremark2", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhremark3))
{
list_param.Add(new MySqlParameter("@dhremark3", data_hotkeysExample.dhremark3));
}
else
{
list_param.Add(new MySqlParameter("@dhremark3", DBNull.Value));
}
if (!string.IsNullOrEmpty(data_hotkeysExample.dhremark4))
{
list_param.Add(new MySqlParameter("@dhremark4", data_hotkeysExample.dhremark4));
}
else
{
list_param.Add(new MySqlParameter("@dhremark4", DBNull.Value));
}
MySqlParameter[] param = new MySqlParameter[list_param.Count];
int index = 0;
foreach (MySqlParameter p in list_param)
{
param[index] = p;
index++;
}
return(param);
}
public bool UpdateServiceLog(string flowNo, DateTime channelRequestTime, DateTime channelReponseTime, DateTime responseTime, string channelResponse, string responseParas)
{
bool flag = false;
//string sql = "update ServiceLog set channelResponse='" + channelResponse + "',responseParas='" + responseParas+"',";
//sql += "channelRequestTime=" + channelRequestTime + ",channelReponseTime=" + channelReponseTime+",";
//sql += "responseTime=" + responseTime;
string sql = "update ServiceLog set ChannelResponse=?ChannelResponse,ResponseParas=?responseParas,";
sql += "ChannelRequestTime=?ChannelRequestTime,ChannelResponseTime=?ChannelResponseTime,";
sql += "ResponseTime=?ResponseTime where FlowNo=?FlowNo";
List <MySqlParameter> paras = new List <MySqlParameter>();
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?FlowNo";
para.Value = flowNo;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.String;
paras.Add(para);
para = new MySqlParameter();
para.ParameterName = "?ChannelResponse";
para.Value = channelResponse;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.String;
paras.Add(para);
para = new MySqlParameter();
para.ParameterName = "?ResponseParas";
para.Value = responseParas;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.String;
paras.Add(para);
para = new MySqlParameter();
para.ParameterName = "?ChannelRequestTime";
para.Value = channelRequestTime;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
para = new MySqlParameter();
para.ParameterName = "?ChannelResponseTime";
para.Value = channelReponseTime;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
para = new MySqlParameter();
para.ParameterName = "?ResponseTime";
para.Value = responseTime;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
flag = DbHelper.ExecuteNonQuery(sql, paras.ToArray()) > 0;
return(flag);
}
public void NewTest()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE Test (id INT, name VARCHAR(50))");
execSQL("CREATE PROCEDURE spTest(theid INT) BEGIN SELECT * FROM test WHERE id=theid; END");
execSQL("INSERT INTO test VALUES (1, 'First')");
execSQL("INSERT INTO test VALUES (2, 'Second')");
execSQL("INSERT INTO test VALUES (3, 'Third')");
execSQL("INSERT INTO test VALUES (4, 'Fourth')");
string connStr = GetPoolingConnectionString();
for (int i = 1; i < 5; i++)
{
using (MySqlConnection con = new MySqlConnection(connStr))
{
con.Open();
MySqlCommand reccmd = new MySqlCommand("spTest", con);
reccmd.CommandTimeout = 0;
reccmd.CommandType = CommandType.StoredProcedure;
MySqlParameter par = new MySqlParameter("@theid", MySqlDbType.String);
par.Value = i;
reccmd.Parameters.Add(par);
using (MySqlDataReader recdr = reccmd.ExecuteReader())
{
if (recdr.Read())
{
int x = recdr.GetOrdinal("name");
Assert.AreEqual(1, x);
}
}
}
}
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
KillConnection(c);
}
public void DifferentParameterOrder()
{
execSQL("CREATE TABLE Test (id int NOT NULL AUTO_INCREMENT, " +
"id2 int NOT NULL, name varchar(50) DEFAULT NULL, " +
"id3 int DEFAULT NULL, PRIMARY KEY (id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, id2, name, id3) " +
"VALUES(?id, ?id2, ?name,?id3)", conn);
MySqlParameter id = new MySqlParameter();
id.ParameterName = "?id";
id.DbType = DbType.Int32;
id.Value = DBNull.Value;
MySqlParameter id2 = new MySqlParameter();
id2.ParameterName = "?id2";
id2.DbType = DbType.Int32;
id2.Value = 2;
MySqlParameter name = new MySqlParameter();
name.ParameterName = "?name";
name.DbType = DbType.String;
name.Value = "Test";
MySqlParameter id3 = new MySqlParameter();
id3.ParameterName = "?id3";
id3.DbType = DbType.Int32;
id3.Value = 3;
cmd.Parameters.Add(id);
cmd.Parameters.Add(id2);
cmd.Parameters.Add(name);
cmd.Parameters.Add(id3);
cmd.Prepare();
Assert.AreEqual(1, cmd.ExecuteNonQuery());
cmd.Parameters.Clear();
id3.Value = DBNull.Value;
name.Value = DBNull.Value;
cmd.Parameters.Add(id);
cmd.Parameters.Add(id2);
cmd.Parameters.Add(id3);
cmd.Parameters.Add(name);
cmd.Prepare();
Assert.AreEqual(1, cmd.ExecuteNonQuery());
cmd.CommandText = "SELECT id3 FROM Test WHERE id=1";
Assert.AreEqual(3, cmd.ExecuteScalar());
cmd.CommandText = "SELECT name FROM Test WHERE id=2";
Assert.AreEqual(DBNull.Value, cmd.ExecuteScalar());
}
public int GetTradeInfoCount(WebTradeCondition queryCondition)
{
int count = 0;
string sql = @"select count(t1.localorderno) from tradeinfo t1 left join
clientinfo t2 on t1.clientID=t2.clientID where tradestatus='Ok' ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and t1.ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ClientName))
{
sql += " and t2.ClientName like ?ClientName";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientName";
para.Value = "%" + queryCondition.ClientName + "%";
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
object obj = DbHelper.ExecuteScalar(sql, paras.ToArray());
if (obj != null)
{
count = int.Parse(obj.ToString());
}
return(count);
}
/// <summary>
/// 获得数据列表
/// </summary>
public IList <ShiMiao.Model.TD_Order_WeiXinPay> GetTopList(int top, string where, string orderBy, IDictionary <string, object> dictParams)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select OrderID, NonceStr, Timestamp, OrgID, WeiXinOrderID, OrderFee, CashFee, Package, Status, PayTime, CallBackTime ");
strSql.Append(" FROM TD_Order_WeiXinPay");
if (!string.IsNullOrEmpty(where))
{
strSql.AppendFormat(" where {0}", where);
}
if (!string.IsNullOrEmpty(orderBy))
{
strSql.AppendFormat(" order by {0}", orderBy);
}
strSql.AppendFormat(" limit {0} ", top);
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (dictParams != null)
{
foreach (var pair in dictParams)
{
MySqlParameter parameter = new MySqlParameter("@" + pair.Key, pair.Value);
parameters.Add(parameter);
}
}
IList <ShiMiao.Model.TD_Order_WeiXinPay> list = new List <ShiMiao.Model.TD_Order_WeiXinPay>();
using (DbDataReader dr = MySqlHelperUtil.ExecuteReader(strSql.ToString(), parameters.ToArray()))
{
while (dr.Read())
{
ShiMiao.Model.TD_Order_WeiXinPay model = new ShiMiao.Model.TD_Order_WeiXinPay();
if (dr[0] != DBNull.Value)
{
model.OrderID = dr.GetString(0);
}
if (dr[1] != DBNull.Value)
{
model.NonceStr = dr.GetString(1);
}
if (dr[2] != DBNull.Value)
{
model.Timestamp = dr.GetInt32(2);
}
if (dr[3] != DBNull.Value)
{
model.OrgID = dr.GetInt32(3);
}
if (dr[4] != DBNull.Value)
{
model.WeiXinOrderID = dr.GetString(4);
}
if (dr[5] != DBNull.Value)
{
model.OrderFee = dr.GetInt32(5);
}
if (dr[6] != DBNull.Value)
{
model.CashFee = dr.GetInt32(6);
}
if (dr[7] != DBNull.Value)
{
model.Package = dr.GetString(7);
}
if (dr[8] != DBNull.Value)
{
model.Status = dr.GetInt32(8);
}
if (dr[9] != DBNull.Value)
{
model.PayTime = dr.GetDateTime(9);
}
if (dr[10] != DBNull.Value)
{
model.CallBackTime = dr.GetDateTime(10);
}
list.Add(model);
}
parameters.Clear();
return(list);
}
}
public DataTable GetTradeInfoPager(WebTradeCondition queryCondition, int pageSize, int pageIndex)
{
DataTable dt = null;
string sql = @"select tradetime, t1.localorderno,t1.clientID,t1.amount,t1.oprid,t1.tradestatus,
t1.tradetype, t2.clientName from tradeinfo t1 left join clientinfo t2 on t1.clientid=t2.clientid
where tradestatus='Ok' ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and t1.ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ClientName))
{
sql += " and t2.ClientName like ?ClientName";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientName";
para.Value = "%" + queryCondition.ClientName + "%";
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
int startIndex = pageSize * (pageIndex - 1);
sql += " limit " + startIndex.ToString() + " ," + pageSize.ToString();;
dt = DbHelper.ExecuteDataTable(sql, paras.ToArray());
return(dt);
}
public int Add(MedicineModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO ARCHIVE_MEDICINE_CN(");
builder.Append("RecordID,IDCardNo,CustomerID,PhysicalID,Energy,Tired,Breath,Voice,Emotion,Spirit,Alone,Fear,");
builder.Append("Weight,Eye,FootHand,Stomach,Cold,Influenza,Nasal,Snore,Allergy,Urticaria,Skin,Scratch,Mouth,");
builder.Append("Arms,Greasy,Spot,Eczema,Thirsty,Smell,Abdomen,Coolfood,Defecate,Defecatedry,Tongue,Vein,");
builder.Append("CreatedBy,CreatedDate,LastUpdateBy,LastUpdateDate,FollowUpDoctor,RecordDate,IsDel)");
builder.Append(" VALUES (");
builder.Append("@RecordID,@IDCardNo,@CustomerID,@PhysicalID,@Energy,@Tired,@Breath,@Voice,@Emotion,@Spirit,@Alone,@Fear,");
builder.Append("@Weight,@Eye,@FootHand,@Stomach,@Cold,@Influenza,@Nasal,@Snore,@Allergy,@Urticaria,@Skin,@Scratch,@Mouth,");
builder.Append("@Arms,@Greasy,@Spot,@Eczema,@Thirsty,@Smell,@Abdomen,@Coolfood,@Defecate,@Defecatedry,@Tongue,@Vein,");
builder.Append("@CreatedBy,@CreatedDate,@LastUpdateBy,@LastUpdateDate,@FollowUpDoctor,@RecordDate,@IsDel)");
builder.Append(";SELECT @@IDENTITY");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@CustomerID", MySqlDbType.String, 17),
new MySqlParameter("@PhysicalID", MySqlDbType.String, 17),
new MySqlParameter("@Energy", MySqlDbType.Decimal),
new MySqlParameter("@Tired", MySqlDbType.Decimal),
new MySqlParameter("@Breath", MySqlDbType.Decimal),
new MySqlParameter("@Voice", MySqlDbType.Decimal),
new MySqlParameter("@Emotion", MySqlDbType.Decimal),
new MySqlParameter("@Spirit", MySqlDbType.Decimal),
new MySqlParameter("@Alone", MySqlDbType.Decimal),
new MySqlParameter("@Fear", MySqlDbType.Decimal),
new MySqlParameter("@Weight", MySqlDbType.Decimal),
new MySqlParameter("@Eye", MySqlDbType.Decimal),
new MySqlParameter("@FootHand", MySqlDbType.Decimal),
new MySqlParameter("@Stomach", MySqlDbType.Decimal),
new MySqlParameter("@Cold", MySqlDbType.Decimal),
new MySqlParameter("@Influenza", MySqlDbType.Decimal),
new MySqlParameter("@Nasal", MySqlDbType.Decimal),
new MySqlParameter("@Snore", MySqlDbType.Decimal),
new MySqlParameter("@Allergy", MySqlDbType.Decimal),
new MySqlParameter("@Urticaria", MySqlDbType.Decimal),
new MySqlParameter("@Skin", MySqlDbType.Decimal),
new MySqlParameter("@Scratch", MySqlDbType.Decimal),
new MySqlParameter("@Mouth", MySqlDbType.Decimal),
new MySqlParameter("@Arms", MySqlDbType.Decimal),
new MySqlParameter("@Greasy", MySqlDbType.Decimal),
new MySqlParameter("@Spot", MySqlDbType.Decimal),
new MySqlParameter("@Eczema", MySqlDbType.Decimal),
new MySqlParameter("@Thirsty", MySqlDbType.Decimal),
new MySqlParameter("@Smell", MySqlDbType.Decimal),
new MySqlParameter("@Abdomen", MySqlDbType.Decimal),
new MySqlParameter("@Coolfood", MySqlDbType.Decimal),
new MySqlParameter("@Defecate", MySqlDbType.Decimal),
new MySqlParameter("@Defecatedry", MySqlDbType.Decimal),
new MySqlParameter("@Tongue", MySqlDbType.Decimal),
new MySqlParameter("@Vein", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.Decimal),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.Decimal),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@FollowUpDoctor", MySqlDbType.String, 20),
new MySqlParameter("@RecordDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1)
};
cmdParms[0].Value = model.RecordID;
cmdParms[1].Value = model.IDCardNo;
cmdParms[2].Value = model.CustomerID;
cmdParms[3].Value = model.PhysicalID;
cmdParms[4].Value = model.Energy;
cmdParms[5].Value = model.Tired;
cmdParms[6].Value = model.Breath;
cmdParms[7].Value = model.Voice;
cmdParms[8].Value = model.Emotion;
cmdParms[9].Value = model.Spirit;
cmdParms[10].Value = model.Alone;
cmdParms[11].Value = model.Fear;
cmdParms[12].Value = model.Weight;
cmdParms[13].Value = model.Eye;
cmdParms[14].Value = model.FootHand;
cmdParms[15].Value = model.Stomach;
cmdParms[16].Value = model.Cold;
cmdParms[17].Value = model.Influenza;
cmdParms[18].Value = model.Nasal;
cmdParms[19].Value = model.Snore;
cmdParms[20].Value = model.Allergy;
cmdParms[21].Value = model.Urticaria;
cmdParms[22].Value = model.Skin;
cmdParms[23].Value = model.Scratch;
cmdParms[24].Value = model.Mouth;
cmdParms[25].Value = model.Arms;
cmdParms[26].Value = model.Greasy;
cmdParms[27].Value = model.Spot;
cmdParms[28].Value = model.Eczema;
cmdParms[29].Value = model.Thirsty;
cmdParms[30].Value = model.Smell;
cmdParms[31].Value = model.Abdomen;
cmdParms[32].Value = model.Coolfood;
cmdParms[33].Value = model.Defecate;
cmdParms[34].Value = model.Defecatedry;
cmdParms[35].Value = model.Tongue;
cmdParms[36].Value = model.Vein;
cmdParms[37].Value = model.CreatedBy;
cmdParms[38].Value = model.CreatedDate;
cmdParms[39].Value = model.LastUpdateBy;
cmdParms[40].Value = model.LastUpdateDate;
cmdParms[41].Value = model.FollowUpDoctor;
cmdParms[42].Value = model.RecordDate;
cmdParms[43].Value = model.IsDel;
object single = MySQLHelper.GetSingle(builder.ToString(), cmdParms);
if (single == null)
{
return(0);
}
return(Convert.ToInt32(single));
}
public ActiveRes Active(string activeCode, string machineCode)
{
// bool flag = false;
ActiveRes res = new ActiveRes();
res.Success = false;
string querySql = "select * from clientinfo where activecode=?ACODE";
string clientID = "";
List <MySqlParameter> paras = new List <MySqlParameter>();
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ACODE";
para.Value = activeCode;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.String;
paras.Add(para);
DataTable dt1 = DbHelper.ExecuteDataTable(querySql, paras.ToArray());
if (dt1 != null && dt1.Rows.Count > 0)
{
clientID = dt1.Rows[0]["clientID"].ToString();
string id = "";
//检查是否已激活过
querySql = "select * from activeinfo where machineCode='" + machineCode + "' and ClientID='" + clientID + "'";
DataTable dt2 = DbHelper.ExecuteDataTable(querySql);
if (dt2 != null && dt2.Rows.Count > 0)
{
id = dt2.Rows[0]["TerminalID"].ToString();
res.Success = true;
res.ClientName = dt1.Rows[0]["ClientName"].ToString();
res.TerminalID = id;
res.ClientID = clientID;
res.PID = dt1.Rows[0]["PID"].ToString();
res.PKEY = dt1.Rows[0]["PKEY"].ToString();
res.AlipayAccount = dt1.Rows[0]["AlipayAccount"].ToString();
}
else
{
//获取已激活数量
int count = 0;
querySql = "select count(*) from activeinfo where clientID='" + clientID + "'";
object obj = DbHelper.ExecuteScalar(querySql);
if (obj != null)
{
count = int.Parse(obj.ToString()) + 1;
}
id = clientID + count.ToString().PadLeft(2, '0');
//插入机器信息
string insertSql = "insert into activeinfo(clientID,machineCode,TerminalID,status) values('";
insertSql += clientID + "','" + machineCode + "','" + id;
insertSql += "',0)";
if (DbHelper.ExecuteNonQuery(insertSql) > 0)
{
res.Success = true;
res.ClientName = dt1.Rows[0]["ClientName"].ToString();
res.TerminalID = id;
res.ClientID = clientID;
res.PID = dt1.Rows[0]["PID"].ToString();
res.PKEY = dt1.Rows[0]["PKEY"].ToString();
res.AlipayAccount = dt1.Rows[0]["AlipayAccount"].ToString();
}
}
}
else
{
res.Success = false;
}
return(res);
// MySqlTransaction trans = (MySqlTransaction)DbHelper.DBConnection.BeginTransaction(); //创建事务
// cmd.Transaction = trans; //绑定事务
//foreach(.........)
//{
// string sql = ".....";
// cmd = new MySQLCommand(sqlInsert, mysqlConn);
// cmd.ExecuteNonQuery();
//}
//trans.Commit();
//return flag;
}
/// <summary>
/// 更新中医体质辨识33个问题项
/// </summary>
/// <param name="model"></param>
/// <param name="customerID"></param>
/// <returns></returns>
public bool UpdateByMiniPad(MedicineModel model, string customerID)
{
StringBuilder builder = new StringBuilder();
builder.Append("UPDATE ARCHIVE_MEDICINE_CN D SET ");
builder.Append("Energy=@Energy,");
builder.Append("Tired=@Tired,");
builder.Append("Breath=@Breath,");
builder.Append("Voice=@Voice,");
builder.Append("Emotion=@Emotion,");
builder.Append("Spirit=@Spirit,");
builder.Append("Alone=@Alone,");
builder.Append("Fear=@Fear,");
builder.Append("Weight=@Weight,");
builder.Append("Eye=@Eye,");
builder.Append("FootHand=@FootHand,");
builder.Append("Stomach=@Stomach,");
builder.Append("Cold=@Cold,");
builder.Append("Influenza=@Influenza,");
builder.Append("Nasal=@Nasal,");
builder.Append("Snore=@Snore,");
builder.Append("Allergy=@Allergy,");
builder.Append("Urticaria=@Urticaria,");
builder.Append("Skin=@Skin,");
builder.Append("Scratch=@Scratch,");
builder.Append("Mouth=@Mouth,");
builder.Append("Arms=@Arms,");
builder.Append("Greasy=@Greasy,");
builder.Append("Spot=@Spot,");
builder.Append("Eczema=@Eczema,");
builder.Append("Thirsty=@Thirsty,");
builder.Append("Smell=@Smell,");
builder.Append("Abdomen=@Abdomen,");
builder.Append("Coolfood=@Coolfood,");
builder.Append("Defecate=@Defecate,");
builder.Append("Defecatedry=@Defecatedry,");
builder.Append("Tongue=@Tongue,");
builder.Append("Vein=@Vein ");
builder.Append(@" WHERE
EXISTS
(
SELECT
D.ID
FROM
ARCHIVE_MEDI_PHYS_DIST med
WHERE D.ID=med.MedicineID
AND med.OutKey=@OutKey
); ");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@Energy", model.Energy),
new MySqlParameter("@Tired", model.Tired),
new MySqlParameter("@Breath", model.Breath),
new MySqlParameter("@Voice", model.Voice),
new MySqlParameter("@Emotion", model.Emotion),
new MySqlParameter("@Spirit", model.Spirit),
new MySqlParameter("@Alone", model.Alone),
new MySqlParameter("@Fear", model.Fear),
new MySqlParameter("@Weight", model.Weight),
new MySqlParameter("@Eye", model.Eye),
new MySqlParameter("@FootHand", model.FootHand),
new MySqlParameter("@Stomach", model.Stomach),
new MySqlParameter("@Cold", model.Cold),
new MySqlParameter("@Influenza", model.Influenza),
new MySqlParameter("@Nasal", model.Nasal),
new MySqlParameter("@Snore", model.Snore),
new MySqlParameter("@Allergy", model.Allergy),
new MySqlParameter("@Urticaria", model.Urticaria),
new MySqlParameter("@Skin", model.Skin),
new MySqlParameter("@Scratch", model.Scratch),
new MySqlParameter("@Mouth", model.Mouth),
new MySqlParameter("@Arms", model.Arms),
new MySqlParameter("@Greasy", model.Greasy),
new MySqlParameter("@Spot", model.Spot),
new MySqlParameter("@Eczema", model.Eczema),
new MySqlParameter("@Thirsty", model.Thirsty),
new MySqlParameter("@Smell", model.Smell),
new MySqlParameter("@Abdomen", model.Abdomen),
new MySqlParameter("@Coolfood", model.Coolfood),
new MySqlParameter("@Defecate", model.Defecate),
new MySqlParameter("@Defecatedry", model.Defecatedry),
new MySqlParameter("@Tongue", model.Tongue),
new MySqlParameter("@Vein", model.Vein),
new MySqlParameter("@OutKey", customerID)
};
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
/// <summary>
/// 根据表,获取一个MySqlParameter数组
/// </summary>
/// <returns>MySqlParameter[]</returns>
public static MySqlParameter[] GetSqlParameters(Users UsersExample)
{
List <MySqlParameter> list_param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(UsersExample.Account))
{
list_param.Add(new MySqlParameter("@Account", UsersExample.Account));
}
else
{
list_param.Add(new MySqlParameter("@Account", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Password))
{
list_param.Add(new MySqlParameter("@Password", UsersExample.Password));
}
else
{
list_param.Add(new MySqlParameter("@Password", DBNull.Value));
}
if (UsersExample.LoginTime != new DateTime() && UsersExample.LoginTime != null)
{
list_param.Add(new MySqlParameter("@LoginTime", UsersExample.LoginTime.ToString("yyyy-MM-dd")));
}
else
{
list_param.Add(new MySqlParameter("@LoginTime", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.State))
{
list_param.Add(new MySqlParameter("@State", UsersExample.State));
}
else
{
list_param.Add(new MySqlParameter("@State", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Name))
{
list_param.Add(new MySqlParameter("@Name", UsersExample.Name));
}
else
{
list_param.Add(new MySqlParameter("@Name", DBNull.Value));
}
if (UsersExample.Birthday != new DateTime() && UsersExample.Birthday != null)
{
list_param.Add(new MySqlParameter("@Birthday", UsersExample.Birthday));
}
else
{
list_param.Add(new MySqlParameter("@Birthday", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Phone))
{
list_param.Add(new MySqlParameter("@Phone", UsersExample.Phone));
}
else
{
list_param.Add(new MySqlParameter("@Phone", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Address))
{
list_param.Add(new MySqlParameter("@Address", UsersExample.Address));
}
else
{
list_param.Add(new MySqlParameter("@Address", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Remark))
{
list_param.Add(new MySqlParameter("@Remark", UsersExample.Remark));
}
else
{
list_param.Add(new MySqlParameter("@Remark", DBNull.Value));
}
if (!string.IsNullOrEmpty(UsersExample.Sex))
{
list_param.Add(new MySqlParameter("@Sex", UsersExample.Sex));
}
else
{
list_param.Add(new MySqlParameter("@Sex", DBNull.Value));
}
MySqlParameter[] param = new MySqlParameter[list_param.Count];
int index = 0;
foreach (MySqlParameter p in list_param)
{
param[index] = p;
index++;
}
return(param);
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
MySqlParameter myParameter = new MySqlParameter("?content", MySqlDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
private void HTMLViewer_Navigating(object sender, WebBrowserNavigatingEventArgs e)
{
dynamic d = HTMLViewer.ActiveXInstance;
d.BeforeNavigate2 += new BeforeNavigate2((object pDisp, ref string url, ref int Flags, ref string TargetFrameName, ref byte[] PostData, ref string Headers, ref bool Cancel) =>
{
string post = string.Empty;
if (PostData != null)
{
post = Encoding.ASCII.GetString(PostData).Trim('\0');
}
if (url == "about:submit_form")
{
if (ready)
{
ready = false;
string[] data = post.Split('&');
if (data[data.Length - 1] == "cancel=Cancel")
{
Parent.ExitInfo();
this.Close();
}
else if (data[data.Length - 1] == "save=Save")
{
// Add form not complete
Parent.ExitInfo();
this.Close();
}
else
{
if (ValidateForm(post))
{
string submission = string.Empty;
for (int i = 0; i < data.Length - 2; i++)
{
submission += data[i] + "&";
}
submission += data[data.Length - 2];
string insertForm = "INSERT INTO user_forms (user_id, form_id, filled_form_file, incomplete, date_of_completion, authorization_complete) VALUES ({0}, {1}, ?formData, 0, '{2}', 1)";
DateTime date = DateTime.Now;
string newdate = date.ToString("yyyy-MM-dd");
insertForm = string.Format(insertForm, UserID, FormID, newdate);
MySqlCommand cmd = new MySqlCommand(insertForm, Parent.Connection);
byte[] dataBytes = Encoding.ASCII.GetBytes(submission);
MySqlParameter formData = new MySqlParameter("?formData", MySqlDbType.Blob, dataBytes.Length);
formData.Value = dataBytes;
cmd.Parameters.Add(formData);
cmd.ExecuteNonQuery();
Parent.ExitInfo();
this.Close();
}
else
{
MessageBox.Show("Missing Required Fields");
Cancel = true;
url = "about:blank";
ready = true;
}
}
}
}
//if (url == "about:index")
//{
// if (ready)
// {
// ready = false;
// string text = File.ReadAllText("");
// HTMLViewer.DocumentText = text;
// }
//}
});
}
private void guardarSocio(String psCorreo, String psNombre, String psContra, String psPregunta, String psRespuesta, String psFechaNac, String psTel, String psHorario, String psFechaIns, int piId_Actividad, String psFoto, int piEstatus)
{
DateTime fecha = DateTime.Now;
String sFecha = fecha.ToString("dd-MM-yyyy");
DateTime fechaIns = DateTime.Now;
String sFechaIns = fechaIns.ToString("dd-MM-yyyy");
MySqlConnection conexion = new MySqlConnection(); //objeto de conexion
String Cadenaconexion; //variable para recibir los valores
//cadena de conexion que va a recibir
Cadenaconexion = "Server=localhost;User id=root; Database=proyecto;Password=;"; //preparar la ruta
conexion.ConnectionString = Cadenaconexion; //a la cadena de conexion se le agrega la conexion
String sInsert = "INSERT INTO socio(correo,nombre,contrasena,pregunta,respuesta,fecha_nac,telefono,horario,id_actividad,foto,estatus,fecha_insc) values " +
"(@correo,@nombre,aes_encrypt(@contrasena,@respuesta),aes_encrypt(@pregunta,@correo),aes_encrypt(@respuesta,'root'),@fechaNac,@Tel,@horario,@idAct,@foto,@estatus,@fechaInsc)";
//String sInsert = "INSERT INTO socio(nombre,fecha_nac,foto,correo,contrasena,pregunta,respuesta,id_actividad,horario,telefono,fecha_insc,estado) values (@nombre,@fechaNac,@foto,@correo,aes_encrypt(@contrasena,@respuesta), @pregunta,aes_encrypt(@respuesta,'s3cret'),@idAct,@horario,@Tel,@fechaInsc,@estatus)";
MySqlCommand comando = new MySqlCommand(sInsert); //query comando
comando.Connection = conexion;
MySqlParameter parametro1 = new MySqlParameter(); //dealaracion del obejto
parametro1.ParameterName = "@correo"; //asigancion
parametro1.Value = psCorreo;
//creacion del objeto
MySqlParameter parametro2 = new MySqlParameter(); //dealaracion del obejto
parametro2.ParameterName = "@nombre"; //asigancion
parametro2.Value = psNombre;
MySqlParameter parametro3 = new MySqlParameter(); //dealaracion del obejto
parametro3.ParameterName = "@contrasena"; //asigancion
parametro3.Value = psContra;
MySqlParameter parametro4 = new MySqlParameter(); //dealaracion del obejto
parametro4.ParameterName = "@pregunta"; //asigancion
parametro4.Value = psPregunta;
MySqlParameter parametro5 = new MySqlParameter(); //dealaracion del obejto
parametro5.ParameterName = "@respuesta"; //asigancion
parametro5.Value = psRespuesta;
MySqlParameter parametro6 = new MySqlParameter(); //dealaracion del obejto
parametro6.ParameterName = "@fechaNac"; //asigancion
parametro6.Value = psFechaNac;
MySqlParameter parametro7 = new MySqlParameter(); //dealaracion del obejto
parametro7.ParameterName = "@Tel"; //asigancion
parametro7.Value = psTel;
MySqlParameter parametro8 = new MySqlParameter(); //dealaracion del obejto
parametro8.ParameterName = "@horario"; //asigancion
parametro8.Value = psHorario;
MySqlParameter parametro9 = new MySqlParameter(); //dealaracion del obejto
parametro9.ParameterName = "@idAct"; //asigancion
parametro9.Value = piId_Actividad;
MySqlParameter parametro10 = new MySqlParameter(); //dealaracion del obejto
parametro10.ParameterName = "@foto"; //asigancion
parametro10.Value = psFoto;
MySqlParameter parametro11 = new MySqlParameter(); //dealaracion del obejto
parametro11.ParameterName = "@estatus"; //asigancion
parametro11.Value = piEstatus;
MySqlParameter parametro12 = new MySqlParameter(); //dealaracion del obejto
parametro12.ParameterName = "@fechaInsc"; //asigancion
parametro12.Value = psFechaIns;
comando.Parameters.Add(parametro1);
comando.Parameters.Add(parametro2);
comando.Parameters.Add(parametro3);
comando.Parameters.Add(parametro4);
comando.Parameters.Add(parametro5);
comando.Parameters.Add(parametro6);
comando.Parameters.Add(parametro7);
comando.Parameters.Add(parametro8);
comando.Parameters.Add(parametro9);
comando.Parameters.Add(parametro10);
comando.Parameters.Add(parametro11);
comando.Parameters.Add(parametro12);
try
{
conexion.Open(); //abrir conexion
int iRegAfec = comando.ExecuteNonQuery(); //ejecutar query
if (iRegAfec == 1)
{
MessageBox.Show("Usuario agregado correctamente", "Confirmacion", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
limpiarCampos();
}
else
{
MessageBox.Show("El usuario no fue agregado", "Confirmacion", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show("Error de tipo: " + ex);
}
conexion.Close();
}
public static Model_CustomerUser ChangePassWord(Model_CustomerUser customerUser)
{
string sql = "";
if (customerUser.Id == 0)
{
//查询用户是否存在
Model_CustomerUser user = GetUserName(customerUser);
if (user != null)
{
throw new Exception("用户已存在");
}
}
if (customerUser.Id != 0)
{
//查询管理员账号是否存在一个
Model_Customer custmer = CustomerServer.GetQueryCustomer(customerUser.CustomerId);
if (custmer.Role == Enum_Role.Administrator && customerUser.Actived == Enum_Active.Disable)
{
List <Model_CustomerUser> counts = GetActived(customerUser);
if (counts.Count <= 1)
{
throw new Exception("不能全部停用，至少要存在一个启用账号");
}
}
Model_CustomerUser use = GetPassword(customerUser);
//if (use.CustomerId== customerUser.CustomerId && use.DisplayName== customerUser.DisplayName && use.UserName== customerUser.UserName && use.Password== customerUser.Password && use.CreateAt== customerUser.CreateAt && use.Actived== customerUser.Actived)
// throw new Exception("密码相同，请修改密码");
if (use.UserName != customerUser.UserName)
{
Model_CustomerUser user = GetUserName(customerUser);
if (user != null)
{
throw new Exception("用户已存在");
}
}
if (use.Password.ToUpper() == MyTool.UserMd5(customerUser.Password).ToUpper())
{
throw new Exception("用户密码已存在，请修改密码");
}
}
if (customerUser.Id == 0)
{
sql = "insert into customer_users(customerId,username,password,displayName,createAt,actived) values(?customerId,?username,?password,?displayName,?createAt,?actived)";
}
else
{
sql = "update customer_users set displayName=?displayName,username=?username,password=?password,actived=?actived where id=?id";
}
MySqlParameter[] para = new MySqlParameter[7];
para[0] = new MySqlParameter("customerId", customerUser.CustomerId);
para[1] = new MySqlParameter("username", customerUser.UserName);
if (customerUser.Id != 0)
{
//判断是否修改过密码
Model_CustomerUser use = GetPassword(customerUser);
if (use.Password.ToUpper() == customerUser.Password.ToUpper())
{
para[2] = new MySqlParameter("password", customerUser.Password.ToUpper());
}
else
{
para[2] = new MySqlParameter("password", MyTool.UserMd5(customerUser.Password).ToUpper());
}
}
else
{
para[2] = new MySqlParameter("password", MyTool.UserMd5(customerUser.Password).ToUpper());
}
para[3] = new MySqlParameter("displayName", customerUser.DisplayName);
para[4] = new MySqlParameter("createAt", customerUser.CreateAt);
para[5] = new MySqlParameter("actived", customerUser.Actived);
para[6] = new MySqlParameter("id", customerUser.Id);
int result = 0;
if (customerUser.Id == 0)
{
result = _SqlHelp.ExecuteNonQuery(sql, para);
}
else
{
result = _SqlHelp.ExecuteNonQuery(sql, para);
}
if (result != 1)
{
throw new Exception("操作失败");
}
return(customerUser);
}
private MySqlParameter Add(MySqlParameter p)
{
return this[mParameters.Add(p)];
}
public int Add(ChronicLungerVisitModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into CD_PTB_VISIT(");
builder.Append("RecordID,CustomerID,IDCardNo,CustomerName,FollowupDate,VisitDoctor,");
builder.Append("CureMonth,Supervisor,FollowupWay,Symptom,SymptomEx,SmokeDayNum,DayDrinkVolume,");
builder.Append("ChemotherapyScheme,MedicationCompliance,DrugType,OmissiveTimes,Adr,AdrEx,");
builder.Append("Complication,ComplicationEx,ReferralOrg,ReferralReason,ReferralResult,");
builder.Append("HandleView,NextVisitDate,StopCureDate,StopCureReason,ShouldVisitTimes,");
builder.Append("VisitTimes,ShouldPharmacyTimes,PharmacyTimes,EstimateDoctor,PharmacyRate,");
builder.Append("CreatedBy,CreatedDate,LastUpdateBy,LastUpdateDate,IsDel,NextSmokeDayNum,NextDayDrinkVolume,VisitCount,OutKey )");
builder.Append(" values (");
builder.Append("@RecordID,@CustomerID,@IDCardNo,@CustomerName,@FollowupDate,@VisitDoctor,");
builder.Append("@CureMonth,@Supervisor,@FollowupWay,@Symptom,@SymptomEx,@SmokeDayNum,@DayDrinkVolume,");
builder.Append("@ChemotherapyScheme,@MedicationCompliance,@DrugType,@OmissiveTimes,@Adr,@AdrEx,");
builder.Append("@Complication,@ComplicationEx,@ReferralOrg,@ReferralReason,@ReferralResult,");
builder.Append("@HandleView,@NextVisitDate,@StopCureDate,@StopCureReason,@ShouldVisitTimes,");
builder.Append("@VisitTimes,@ShouldPharmacyTimes,@PharmacyTimes,@EstimateDoctor,@PharmacyRate,");
builder.Append("@CreatedBy,@CreatedDate,@LastUpdateBy,@LastUpdateDate,@IsDel,@NextSmokeDayNum,@NextDayDrinkVolume,@VisitCount,@OutKey)");
builder.Append(";select @@IDENTITY");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@CustomerID", MySqlDbType.String, 32),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@CustomerName", MySqlDbType.String, 30),
new MySqlParameter("@FollowupDate", MySqlDbType.Date),
new MySqlParameter("@VisitDoctor", MySqlDbType.String, 30),
new MySqlParameter("@CureMonth", MySqlDbType.String, 100),
new MySqlParameter("@Supervisor", MySqlDbType.String, 100),
new MySqlParameter("@FollowupWay", MySqlDbType.String, 1),
new MySqlParameter("@Symptom", MySqlDbType.String, 20),
new MySqlParameter("@SymptomEx", MySqlDbType.String, 100),
new MySqlParameter("@SmokeDayNum", MySqlDbType.Decimal),
new MySqlParameter("@DayDrinkVolume", MySqlDbType.Decimal),
new MySqlParameter("@ChemotherapyScheme", MySqlDbType.String, 100),
new MySqlParameter("@MedicationCompliance", MySqlDbType.String, 1),
new MySqlParameter("@DrugType", MySqlDbType.String, 100),
new MySqlParameter("@OmissiveTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@Adr", MySqlDbType.String, 1),
new MySqlParameter("@AdrEx", MySqlDbType.String, 100),
new MySqlParameter("@Complication", MySqlDbType.String, 1),
new MySqlParameter("@ComplicationEx", MySqlDbType.String, 100),
new MySqlParameter("@ReferralOrg", MySqlDbType.String, 100),
new MySqlParameter("@ReferralReason", MySqlDbType.String, 100),
new MySqlParameter("@ReferralResult", MySqlDbType.String, 100),
new MySqlParameter("@HandleView", MySqlDbType.String, 100),
new MySqlParameter("@NextVisitDate", MySqlDbType.Date),
new MySqlParameter("@StopCureDate", MySqlDbType.Date),
new MySqlParameter("@StopCureReason", MySqlDbType.String, 100),
new MySqlParameter("@ShouldVisitTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@VisitTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@ShouldPharmacyTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@PharmacyTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@EstimateDoctor", MySqlDbType.String, 30),
new MySqlParameter("@PharmacyRate", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.String, 30),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.String, 30),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@NextSmokeDayNum", MySqlDbType.Decimal),
new MySqlParameter("@NextDayDrinkVolume", MySqlDbType.Decimal),
new MySqlParameter("@VisitCount", MySqlDbType.Int32, 1),
new MySqlParameter("@OutKey", MySqlDbType.Int32, 4)
};
cmdParms[0].Value = model.RecordID;
cmdParms[1].Value = model.CustomerID;
cmdParms[2].Value = model.IDCardNo;
cmdParms[3].Value = model.CustomerName;
cmdParms[4].Value = model.FollowupDate;
cmdParms[5].Value = model.VisitDoctor;
cmdParms[6].Value = model.CureMonth;
cmdParms[7].Value = model.Supervisor;
cmdParms[8].Value = model.FollowupWay;
cmdParms[9].Value = model.Symptom;
cmdParms[10].Value = model.SymptomEx;
cmdParms[11].Value = model.SmokeDayNum;
cmdParms[12].Value = model.DayDrinkVolume;
cmdParms[13].Value = model.ChemotherapyScheme;
cmdParms[14].Value = model.MedicationCompliance;
cmdParms[15].Value = model.DrugType;
cmdParms[16].Value = model.OmissiveTimes;
cmdParms[17].Value = model.Adr;
cmdParms[18].Value = model.AdrEx;
cmdParms[19].Value = model.Complication;
cmdParms[20].Value = model.ComplicationEx;
cmdParms[21].Value = model.ReferralOrg;
cmdParms[22].Value = model.ReferralReason;
cmdParms[23].Value = model.ReferralResult;
cmdParms[24].Value = model.HandleView;
cmdParms[25].Value = model.NextVisitDate;
cmdParms[26].Value = model.StopCureDate;
cmdParms[27].Value = model.StopCureReason;
cmdParms[28].Value = model.ShouldVisitTimes;
cmdParms[29].Value = model.VisitTimes;
cmdParms[30].Value = model.ShouldPharmacyTimes;
cmdParms[31].Value = model.PharmacyTimes;
cmdParms[32].Value = model.EstimateDoctor;
cmdParms[33].Value = model.PharmacyRate;
cmdParms[34].Value = model.CreatedBy;
cmdParms[35].Value = model.CreatedDate;
cmdParms[36].Value = model.LastUpdateBy;
cmdParms[37].Value = model.LastUpdateDate;
cmdParms[38].Value = model.IsDel;
cmdParms[39].Value = model.NextSmokeDayNum;
cmdParms[40].Value = model.NextDayDrinkVolume;
cmdParms[41].Value = model.VisitCount;
cmdParms[42].Value = model.OUTKey;
object single = MySQLHelper.GetSingle(builder.ToString(), cmdParms);
if (single == null)
{
return(0);
}
return(Convert.ToInt32(single));
}
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn)
{
MySqlDataReader reader;
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
command.CommandType = CommandType.Text;
if (commandParameters != null)
{
foreach (MySqlParameter parameter in commandParameters)
{
command.Parameters.Add(parameter);
}
}
if (ExternalConn)
{
reader = command.ExecuteReader();
}
else
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
command.Parameters.Clear();
return reader;
}
public bool Update(ChronicLungerVisitModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("update CD_PTB_VISIT set ");
builder.Append("RecordID=@RecordID,");
builder.Append("CustomerID=@CustomerID,");
builder.Append("IDCardNo=@IDCardNo,");
builder.Append("CustomerName=@CustomerName,");
builder.Append("FollowupDate=@FollowupDate,");
builder.Append("VisitDoctor=@VisitDoctor,");
builder.Append("CureMonth=@CureMonth,");
builder.Append("Supervisor=@Supervisor,");
builder.Append("FollowupWay=@FollowupWay,");
builder.Append("Symptom=@Symptom,");
builder.Append("SymptomEx=@SymptomEx,");
builder.Append("SmokeDayNum=@SmokeDayNum,");
builder.Append("DayDrinkVolume=@DayDrinkVolume,");
builder.Append("ChemotherapyScheme=@ChemotherapyScheme,");
builder.Append("MedicationCompliance=@MedicationCompliance,");
builder.Append("DrugType=@DrugType,");
builder.Append("OmissiveTimes=@OmissiveTimes,");
builder.Append("Adr=@Adr,");
builder.Append("AdrEx=@AdrEx,");
builder.Append("Complication=@Complication,");
builder.Append("ComplicationEx=@ComplicationEx,");
builder.Append("ReferralOrg=@ReferralOrg,");
builder.Append("ReferralReason=@ReferralReason,");
builder.Append("ReferralResult=@ReferralResult,");
builder.Append("HandleView=@HandleView,");
builder.Append("NextVisitDate=@NextVisitDate,");
builder.Append("StopCureDate=@StopCureDate,");
builder.Append("StopCureReason=@StopCureReason,");
builder.Append("ShouldVisitTimes=@ShouldVisitTimes,");
builder.Append("VisitTimes=@VisitTimes,");
builder.Append("ShouldPharmacyTimes=@ShouldPharmacyTimes,");
builder.Append("PharmacyTimes=@PharmacyTimes,");
builder.Append("EstimateDoctor=@EstimateDoctor,");
builder.Append("PharmacyRate=@PharmacyRate,");
builder.Append("CreatedBy=@CreatedBy,");
builder.Append("CreatedDate=@CreatedDate,");
builder.Append("LastUpdateBy=@LastUpdateBy,");
builder.Append("LastUpdateDate=@LastUpdateDate,");
builder.Append("IsDel=@IsDel,");
builder.Append("NextSmokeDayNum=@NextSmokeDayNum,");
builder.Append("NextDayDrinkVolume=@NextDayDrinkVolume,");
builder.Append("VisitCount = @VisitCount");
builder.Append(" where OutKey=@OutKey And VisitCount=@VisitCount");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@CustomerID", MySqlDbType.String, 32),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@CustomerName", MySqlDbType.String, 30),
new MySqlParameter("@FollowupDate", MySqlDbType.Date),
new MySqlParameter("@VisitDoctor", MySqlDbType.String, 30),
new MySqlParameter("@CureMonth", MySqlDbType.String, 100),
new MySqlParameter("@Supervisor", MySqlDbType.String, 100),
new MySqlParameter("@FollowupWay", MySqlDbType.String, 1),
new MySqlParameter("@Symptom", MySqlDbType.String, 20),
new MySqlParameter("@SymptomEx", MySqlDbType.String, 100),
new MySqlParameter("@SmokeDayNum", MySqlDbType.Decimal),
new MySqlParameter("@DayDrinkVolume", MySqlDbType.Decimal),
new MySqlParameter("@ChemotherapyScheme", MySqlDbType.String, 100),
new MySqlParameter("@MedicationCompliance", MySqlDbType.String, 1),
new MySqlParameter("@DrugType", MySqlDbType.String, 100),
new MySqlParameter("@OmissiveTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@Adr", MySqlDbType.String, 1),
new MySqlParameter("@AdrEx", MySqlDbType.String, 100),
new MySqlParameter("@Complication", MySqlDbType.String, 1),
new MySqlParameter("@ComplicationEx", MySqlDbType.String, 100),
new MySqlParameter("@ReferralOrg", MySqlDbType.String, 100),
new MySqlParameter("@ReferralReason", MySqlDbType.String, 100),
new MySqlParameter("@ReferralResult", MySqlDbType.String, 100),
new MySqlParameter("@HandleView", MySqlDbType.String, 100),
new MySqlParameter("@NextVisitDate", MySqlDbType.Date),
new MySqlParameter("@StopCureDate", MySqlDbType.Date),
new MySqlParameter("@StopCureReason", MySqlDbType.String, 100),
new MySqlParameter("@ShouldVisitTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@VisitTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@ShouldPharmacyTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@PharmacyTimes", MySqlDbType.Int32, 20),
new MySqlParameter("@EstimateDoctor", MySqlDbType.String, 30),
new MySqlParameter("@PharmacyRate", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.String, 30),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.String, 30),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@NextSmokeDayNum", MySqlDbType.Decimal),
new MySqlParameter("@NextDayDrinkVolume", MySqlDbType.Decimal),
new MySqlParameter("@VisitCount", MySqlDbType.Int32, 1),
new MySqlParameter("@OutKey", MySqlDbType.Int32, 8)
};
cmdParms[0].Value = model.RecordID;
cmdParms[1].Value = model.CustomerID;
cmdParms[2].Value = model.IDCardNo;
cmdParms[3].Value = model.CustomerName;
cmdParms[4].Value = model.FollowupDate;
cmdParms[5].Value = model.VisitDoctor;
cmdParms[6].Value = model.CureMonth;
cmdParms[7].Value = model.Supervisor;
cmdParms[8].Value = model.FollowupWay;
cmdParms[9].Value = model.Symptom;
cmdParms[10].Value = model.SymptomEx;
cmdParms[11].Value = model.SmokeDayNum;
cmdParms[12].Value = model.DayDrinkVolume;
cmdParms[13].Value = model.ChemotherapyScheme;
cmdParms[14].Value = model.MedicationCompliance;
cmdParms[15].Value = model.DrugType;
cmdParms[16].Value = model.OmissiveTimes;
cmdParms[17].Value = model.Adr;
cmdParms[18].Value = model.AdrEx;
cmdParms[19].Value = model.Complication;
cmdParms[20].Value = model.ComplicationEx;
cmdParms[21].Value = model.ReferralOrg;
cmdParms[22].Value = model.ReferralReason;
cmdParms[23].Value = model.ReferralResult;
cmdParms[24].Value = model.HandleView;
cmdParms[25].Value = model.NextVisitDate;
cmdParms[26].Value = model.StopCureDate;
cmdParms[27].Value = model.StopCureReason;
cmdParms[28].Value = model.ShouldVisitTimes;
cmdParms[29].Value = model.VisitTimes;
cmdParms[30].Value = model.ShouldPharmacyTimes;
cmdParms[31].Value = model.PharmacyTimes;
cmdParms[32].Value = model.EstimateDoctor;
cmdParms[33].Value = model.PharmacyRate;
cmdParms[34].Value = model.CreatedBy;
cmdParms[35].Value = model.CreatedDate;
cmdParms[36].Value = model.LastUpdateBy;
cmdParms[37].Value = model.LastUpdateDate;
cmdParms[38].Value = model.IsDel;
cmdParms[39].Value = model.NextSmokeDayNum;
cmdParms[40].Value = model.NextDayDrinkVolume;
cmdParms[41].Value = model.VisitCount;
cmdParms[42].Value = model.OUTKey;
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
public void SetDbType()
{
IDbCommand cmd = conn.CreateCommand();
IDbDataParameter prm = cmd.CreateParameter();
prm.DbType = DbType.Int64;
Assert.AreEqual(DbType.Int64, prm.DbType);
prm.Value = 3;
Assert.AreEqual(DbType.Int64, prm.DbType);
MySqlParameter p = new MySqlParameter("name", MySqlDbType.Int64);
Assert.AreEqual(DbType.Int64, p.DbType);
Assert.AreEqual(MySqlDbType.Int64, p.MySqlDbType);
p.Value = 3;
Assert.AreEqual(DbType.Int64, p.DbType);
Assert.AreEqual(MySqlDbType.Int64, p.MySqlDbType);
}
private void BtnFacturar_Click(object sender, RoutedEventArgs e)
{
int lastFactura = 0;
int lastDetalle = 0;
string[] cadenaCliente;
int idCliente = 0;
double totalFactura;
int vendedor = 0;
int cantidad;
// descripcion tiene el id del producto separado por guion
string[] descripcion;
double precioUnidad;
double valorTotal;
bool banderaPedido;
if (textBox.Text.Equals(""))
{
System.Windows.MessageBox.Show("Creando orden para despachar");
}
else
{
try
{
cadenaCliente = textBox.Text.Split('-');
idCliente = int.Parse(cadenaCliente[1]);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Compruebe cliente");
throw;
}
}
try
{
MySqlCommand cmd;
//obtenemos el id del cliente
//obtenermos la sumatoria total
//double no int OJO
totalFactura = int.Parse(txtTotal.Text);
DateTime time = DateTime.Now;
//obtenemos el vendedor que por el momento sera estatico
//ACA VA USUARIO O VENDEDOR
vendedor = 1;
connection.Open();
string fechaHora = time.ToString(@"dd/MM/yyyy hh:mm:ss tt", new System.Globalization.CultureInfo("en-US"));
if (textBox.Text.Equals(""))
{
cmd = new MySqlCommand("insert into factura(fecha,idVendedor,total,cobro) values(str_to_date('" + fechaHora + "', '%d/%m/%Y %h:%i:%s %p') , " + vendedor.ToString() + "," + totalFactura.ToString() + ",0)", connection);
}
else
{
cmd = new MySqlCommand("insert into factura(fecha,idVendedor,idCliente,total,cobro) values(str_to_date('" + fechaHora + "', '%d/%m/%Y %h:%i:%s %p') , " + vendedor.ToString() + "," + idCliente.ToString() + "," + totalFactura.ToString() + ",1)", connection);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("_________");
Console.WriteLine(ex);
Console.WriteLine("_________");
}
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw;
}
try
{
//obtenemos la ultima factura para ser procesados con su detalle correspondiente
connection.Open();
MySqlCommand cmd2 = new MySqlCommand("select max(id) from factura ", connection);
MySqlDataReader idLastFactura = cmd2.ExecuteReader();
while (idLastFactura.Read())
{
lastFactura = int.Parse(idLastFactura.GetString(0));
}
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine("_________");
Console.WriteLine(ex);
Console.WriteLine("_________");
throw;
}
foreach (DataRow row in dt_orden.Rows)
{
cantidad = (int)row[0];
// descripcion tiene el id del producto separado por guion
descripcion = row[1].ToString().Split('-');
precioUnidad = (double)row[2];
valorTotal = (double)row[3];
banderaPedido = (bool)row[4];
try
{
if (lastFactura != 0)
{
connection.Open();
MySqlParameter[] pms = new MySqlParameter[5];
pms[0] = new MySqlParameter("idfactura", lastFactura);
pms[1] = new MySqlParameter("quantity", cantidad);
pms[2] = new MySqlParameter("precioUnitario", valorTotal);
pms[3] = new MySqlParameter("idPrendaDebitar", Int32.Parse(descripcion[1]));
pms[4] = new MySqlParameter("idBodegaDebitar", 1);
MySqlCommand command = new MySqlCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "addDetalleFactura";
command.Connection = connection;
command.Parameters.Add("@res", MySqlDbType.VarChar).Direction = System.Data.ParameterDirection.Output;
command.Parameters.AddRange(pms);
if (command.ExecuteNonQuery() == 1)
{
//MessageBox.Show("yes");
Console.WriteLine(Convert.ToString(command.Parameters["@res"].Value));
if (Convert.ToString(command.Parameters["@res"].Value).Equals("1"))
{
System.Windows.MessageBox.Show("Revisar Inventario en tienda ");
}
}
connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("_________");
Console.WriteLine(ex);
Console.WriteLine("_________");
throw;
}
if (banderaPedido)
{
DateTime fecha = DateTime.Now;
//obtener el ultimo detalle
connection.Open();
MySqlCommand cmdLastid = new MySqlCommand("select max(idDetalle) from detalle", connection);
MySqlDataReader idLastDetalle = cmdLastid.ExecuteReader();
while (idLastDetalle.Read())
{
lastDetalle = int.Parse(idLastDetalle.GetString(0));
}
connection.Close();
connection.Open();
string insertPedido = "insert into pedido(idDetalle , idPrenda , cantidad, observacion , fecha_emision , fecha_entrega , estado) " +
"values(" + lastDetalle + "," + descripcion[1] + "," + cantidad + ",'pendiente de entrega',str_to_date('" + fecha.ToString("MM/d/yyyy") + "', '%m/%d/%Y'), str_to_date('" + fecha.AddDays(20).ToString("MM/d/yyyy") + "', '%m/%d/%Y'),0)";
MySqlCommand cmd3 = new MySqlCommand(insertPedido, connection);
cmd3.ExecuteNonQuery();
connection.Close();
//insertando en detalle para facturar
//connection.Open();
//string insertDetallesSinRebajar = "insert into detalle(cantidad, precioTotal , idPrenda, idFactura ) values("+cantidad+" ,"+ valorTotal + " , "+ Int32.Parse(descripcion[1]) + " , "+ lastFactura + " ); "
}
else
{
Console.WriteLine("no pedido");
// ################### agregar lo de rebajar aca ############################################
connection.Open();
MySqlParameter[] pms = new MySqlParameter[5];
pms[0] = new MySqlParameter("idfactura", lastFactura);
pms[1] = new MySqlParameter("quantity", cantidad);
pms[2] = new MySqlParameter("precioUnitario", valorTotal);
pms[3] = new MySqlParameter("idPrendaDebitar", Int32.Parse(descripcion[1]));
pms[4] = new MySqlParameter("idBodegaDebitar", 1);
MySqlCommand command = new MySqlCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "addRebajaInventario";
command.Connection = connection;
command.Parameters.Add("@res", MySqlDbType.VarChar).Direction = System.Data.ParameterDirection.Output;
command.Parameters.AddRange(pms);
if (command.ExecuteNonQuery() == 1)
{
//MessageBox.Show("yes");
Console.WriteLine(Convert.ToString(command.Parameters["@res"].Value));
if (Convert.ToString(command.Parameters["@res"].Value).Equals("1"))
{
System.Windows.MessageBox.Show("Revisar Inventario en tienda ");
}
}
connection.Close();
}
}
this.Close();
}
//
// 摘要:
// 将所提供的 System.Data.IDataReader 中的所有行复制到 System.Data.SqlClient.SqlBulkCopy
// 对象的 System.Data.SqlClient.SqlBulkCopy.DestinationTableName 属性指定的目标表中。
//
// 参数:
// reader:
// 一个 System.Data.IDataReader，它的行将被复制到目标表中。
public void WriteToServer(IDataReader reader)
{
while (true)
{
int nCount = 0;
using (MySqlTransaction trans = m_connection.BeginTransaction())
{
using (MySqlCommand command = new MySqlCommand("",
m_connection))
{
string strKeyParamName = "@k";
string strFromParamName = "@f";
string strIdParamName = "@i";
string strKeynumParamName = "@n";
string strCommand = " INSERT INTO " + this.DestinationTableName
+ " (keystring,fromstring,idstring,keystringnum) "
+ " VALUES (" + strKeyParamName + ","
+ strFromParamName + ","
+ strIdParamName + ","
+ strKeynumParamName + ") ;";
command.CommandText = strCommand;
command.Prepare();
MySqlParameter param_key = new MySqlParameter();
param_key.ParameterName = strKeyParamName;
MySqlParameter param_from = new MySqlParameter();
param_from.ParameterName = strFromParamName;
MySqlParameter param_id = new MySqlParameter();
param_id.ParameterName = strIdParamName;
MySqlParameter param_num = new MySqlParameter();
param_num.ParameterName = strKeynumParamName;
command.Parameters.Add(param_key);
command.Parameters.Add(param_from);
command.Parameters.Add(param_id);
command.Parameters.Add(param_num);
while (reader.Read())
{
param_key.Value = (string)reader["keystring"];
param_from.Value = (string)reader["fromstring"];
param_id.Value = (string)reader["idstring"];
param_num.Value = (string)reader["keystringnum"];
command.ExecuteNonQuery();
nCount++;
if (this.BatchSize != 0 && nCount >= this.BatchSize)
goto END1;
}
trans.Commit();
return; // 终于完成
} // end of using command
END1:
trans.Commit();
}
}
}
public static int AddUserPage(
Guid userPageId,
Guid siteGuid,
int siteId,
Guid userGuid,
string pageName,
string pagePath,
int pageOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_UserPages ");
sqlCommand.Append("( ");
sqlCommand.Append("UserPageID, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("PageName, ");
sqlCommand.Append("PagePath, ");
sqlCommand.Append("PageOrder ");
sqlCommand.Append(")");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?UserPageID, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?SiteID, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?PageName, ");
sqlCommand.Append("?PagePath, ");
sqlCommand.Append("?PageOrder ");
sqlCommand.Append(");");
MySqlParameter[] arParams = new MySqlParameter[7];
arParams[0] = new MySqlParameter("?UserPageID", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userPageId.ToString();
arParams[1] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteId;
arParams[2] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userGuid.ToString();
arParams[3] = new MySqlParameter("?PageName", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = pageName;
arParams[4] = new MySqlParameter("?PagePath", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pagePath;
arParams[5] = new MySqlParameter("?PageOrder", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pageOrder;
arParams[6] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = siteGuid.ToString();
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public void TypeCoercion()
{
MySqlParameter p = new MySqlParameter("?test", 1);
Assert.AreEqual(DbType.Int32, p.DbType);
Assert.AreEqual(MySqlDbType.Int32, p.MySqlDbType);
p.DbType = DbType.Int64;
Assert.AreEqual(DbType.Int64, p.DbType);
Assert.AreEqual(MySqlDbType.Int64, p.MySqlDbType);
p.MySqlDbType = MySqlDbType.Int16;
Assert.AreEqual(DbType.Int16, p.DbType);
Assert.AreEqual(MySqlDbType.Int16, p.MySqlDbType);
}
/// <summary>
/// 根据传入Model更新数据并返回更新后的Model
/// </summary>
public int UpdateHouseInfo(DBHouseInfo dbHouseInfo)
{
string sql =
"UPDATE HouseInfos " +
"SET " +
" HouseTitle = @HouseTitle"
+ ", HouseOnlineURL = @HouseOnlineURL"
+ ", HouseLocation = @HouseLocation"
+ ", DisPlayPrice = @DisPlayPrice"
+ ", HousePrice = @HousePrice"
+ ", LocationCityName = @LocationCityName"
+ ", Source = @Source"
+ ", HouseText = @HouseText"
+ ", IsAnalyzed = @IsAnalyzed"
+ ", Status = @Status"
+ " WHERE Id = @Id";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter()
{
ParameterName = "@Id", Value = dbHouseInfo.Id, DbType = DbType.Int64
},
new MySqlParameter()
{
ParameterName = "@HouseTitle", Value = dbHouseInfo.HouseTitle, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@HouseOnlineURL", Value = dbHouseInfo.HouseOnlineURL, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@HouseLocation", Value = dbHouseInfo.HouseLocation, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@DisPlayPrice", Value = dbHouseInfo.DisPlayPrice, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@HousePrice", Value = dbHouseInfo.HousePrice, DbType = DbType.Decimal
},
new MySqlParameter()
{
ParameterName = "@LocationCityName", Value = dbHouseInfo.LocationCityName, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@Source", Value = dbHouseInfo.Source, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@HouseText", Value = dbHouseInfo.HouseText, DbType = DbType.String
},
new MySqlParameter()
{
ParameterName = "@IsAnalyzed", Value = dbHouseInfo.HouseText, DbType = DbType.Boolean
},
new MySqlParameter()
{
ParameterName = "@Status", Value = dbHouseInfo.Status, DbType = DbType.Int32
}
};
return(MyDBHelper.ExecuteNonQuery(sql, para));
}
public static int AddSharedFileFolder(
Guid folderGuid,
Guid moduleGuid,
Guid parentGuid,
int moduleId,
string folderName,
int parentId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SharedFileFolders (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("FolderName, ");
sqlCommand.Append("ParentID, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("FolderGuid, ");
sqlCommand.Append("ParentGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?ModuleID, ");
sqlCommand.Append("?FolderName, ");
sqlCommand.Append("?ParentID, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?FolderGuid, ");
sqlCommand.Append("?ParentGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[6];
arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new MySqlParameter("?FolderName", MySqlDbType.VarChar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = folderName;
arParams[2] = new MySqlParameter("?ParentID", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = parentId;
arParams[3] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = moduleGuid.ToString();
arParams[4] = new MySqlParameter("?FolderGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = folderGuid.ToString();
arParams[5] = new MySqlParameter("?ParentGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = parentGuid.ToString();
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static bool UpdateSharedFile(
int itemId,
int moduleId,
int uploadUserId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int folderId,
Guid folderGuid,
Guid userGuid,
string description)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SharedFiles ");
sqlCommand.Append("SET ");
sqlCommand.Append("ModuleID = ?ModuleID, ");
sqlCommand.Append("UploadUserID = ?UploadUserID, ");
sqlCommand.Append("FriendlyName = ?FriendlyName, ");
sqlCommand.Append("OriginalFileName = ?OriginalFileName, ");
sqlCommand.Append("ServerFileName = ?ServerFileName, ");
sqlCommand.Append("SizeInKB = ?SizeInKB, ");
sqlCommand.Append("UploadDate = ?UploadDate, ");
sqlCommand.Append("FolderID = ?FolderID, ");
sqlCommand.Append("UserGuid = ?UserGuid, ");
sqlCommand.Append("Description = ?Description, ");
sqlCommand.Append("FolderGuid = ?FolderGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemID = ?ItemID ;");
MySqlParameter[] arParams = new MySqlParameter[12];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new MySqlParameter("?UploadUserID", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = uploadUserId;
arParams[3] = new MySqlParameter("?FriendlyName", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = friendlyName;
arParams[4] = new MySqlParameter("?OriginalFileName", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = originalFileName;
arParams[5] = new MySqlParameter("?ServerFileName", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = serverFileName;
arParams[6] = new MySqlParameter("?SizeInKB", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = sizeInKB;
arParams[7] = new MySqlParameter("?UploadDate", MySqlDbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadDate;
arParams[8] = new MySqlParameter("?FolderID", MySqlDbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = folderId;
arParams[9] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userGuid.ToString();
arParams[10] = new MySqlParameter("?FolderGuid", MySqlDbType.VarChar, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = folderGuid.ToString();
arParams[11] = new MySqlParameter("?Description", MySqlDbType.LongText);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = description;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
///
/// </summary>
/// <param name="ParameterName"></param>
/// <param name="DataType"></param>
/// <param name="Direction"></param>
/// <returns></returns>
internal static MySqlParameter CreateOutputParameter(string ParameterName,
MySqlType DataType, ParameterDirection Direction)
{
MySqlParameter param = new MySqlParameter(ParameterName, DataType);
param.Direction = Direction;
return param;
}
public static bool AddHistory(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int itemId,
int moduleId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int uploadUserId,
DateTime archiveDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SharedFilesHistory (");
sqlCommand.Append("ItemID, ");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("FriendlyName, ");
sqlCommand.Append("OriginalFileName, ");
sqlCommand.Append("ServerFileName, ");
sqlCommand.Append("SizeInKB, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("UploadUserID, ");
sqlCommand.Append("ArchiveDate, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?ItemID, ");
sqlCommand.Append("?ModuleID, ");
sqlCommand.Append("?FriendlyName, ");
sqlCommand.Append("?OriginalFileName, ");
sqlCommand.Append("?ServerFileName, ");
sqlCommand.Append("?SizeInKB, ");
sqlCommand.Append("?UploadDate, ");
sqlCommand.Append("?UploadUserID, ");
sqlCommand.Append("?ArchiveDate, ");
sqlCommand.Append("?ItemGuid, ");
sqlCommand.Append("?ModuleGuid, ");
sqlCommand.Append("?UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[12];
arParams[0] = new MySqlParameter("?ItemID", MySqlDbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new MySqlParameter("?FriendlyName", MySqlDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new MySqlParameter("?OriginalFileName", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new MySqlParameter("?ServerFileName", MySqlDbType.VarChar, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new MySqlParameter("?SizeInKB", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new MySqlParameter("?UploadDate", MySqlDbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new MySqlParameter("?UploadUserID", MySqlDbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadUserId;
arParams[8] = new MySqlParameter("?ArchiveDate", MySqlDbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = archiveDate;
arParams[9] = new MySqlParameter("?ItemGuid", MySqlDbType.VarChar, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = itemGuid.ToString();
arParams[10] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moduleGuid.ToString();
arParams[11] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userGuid.ToString();
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID > 0);
}
public void MoreParametersOutOfOrder()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE `Test` (`BlackListID` int(11) NOT NULL auto_increment, " +
"`SubscriberID` int(11) NOT NULL, `Phone` varchar(50) default NULL, " +
"`ContactID` int(11) default NULL, " +
"`AdminJunk` tinyint(1) NOT NULL default '0', " +
"PRIMARY KEY (`BlackListID`), KEY `SubscriberID` (`SubscriberID`))");
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO `Test`(`SubscriberID`,`Phone`,`ContactID`, " +
"`AdminJunk`) VALUES (?SubscriberID,?Phone,?ContactID, ?AdminJunk);";
MySqlParameter oParameterSubscriberID = new MySqlParameter();
oParameterSubscriberID.ParameterName = "?SubscriberID";
oParameterSubscriberID.DbType = DbType.Int32;
oParameterSubscriberID.Value = 1;
MySqlParameter oParameterPhone = new MySqlParameter();
oParameterPhone.ParameterName = "?Phone";
oParameterPhone.DbType = DbType.String;
oParameterPhone.Value = DBNull.Value;
MySqlParameter oParameterContactID = new MySqlParameter();
oParameterContactID.ParameterName = "?ContactID";
oParameterContactID.DbType = DbType.Int32;
oParameterContactID.Value = DBNull.Value;
MySqlParameter oParameterAdminJunk = new MySqlParameter();
oParameterAdminJunk.ParameterName = "?AdminJunk";
oParameterAdminJunk.DbType = DbType.Boolean;
oParameterAdminJunk.Value = true;
cmd.Parameters.Add(oParameterSubscriberID);
cmd.Parameters.Add(oParameterPhone);
cmd.Parameters.Add(oParameterAdminJunk);
cmd.Parameters.Add(oParameterContactID);
cmd.Prepare();
int cnt = cmd.ExecuteNonQuery();
Assert.AreEqual(1, cnt);
}
/// <summary>
/// Prepare a DbCommand object with its parameters
/// </summary>
/// <param name="pStoredProcedureName">Stored procedure name using dbo.Name sintax</param>
/// <param name="pParameters">Array of DataParameter</param>
/// <param name="pOutputParameters">List of DataParameters that are for output purpose</param>
/// <param name="pDbAccess">Database Object</param>
/// <returns>Prepared DbCommand</returns>
protected virtual DbCommand PrepareCommand(string pStoredProcedureName, IEnumerable<DataParameter> pParameters, out IList<DataParameter> pOutputParameters, out Database pDbAccess)
{
pDbAccess = GetDatabase();
DbCommand command = pDbAccess.GetStoredProcCommand(pStoredProcedureName);
pOutputParameters = new List<DataParameter>();
pOutputParameters.Add(new DataParameter(RETURN_VALUE, DbType.Int32, ParameterDirection.ReturnValue, 0));
MySqlParameter param = new MySqlParameter(RETURN_VALUE, MySqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
foreach (DataParameter parameter in pParameters)
{
pDbAccess.AddParameter(command, parameter.ParameterName, parameter.DbType, parameter.Direction, parameter.ParameterName, DataRowVersion.Current, parameter.Value);
if (parameter.Direction==ParameterDirection.Output || parameter.Direction==ParameterDirection.InputOutput)
{
pOutputParameters.Add(parameter);
}
}
return command;
}
