public void EntityUpdateTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
TestEntityDataProvider provider = new TestEntityDataProvider(fakeDb);
TestEntity entity = new TestEntity() { Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8 };
String expectedSql = "UPDATE TestTable SET TestColumn1=@PN_IDX_0,TestColumn2=@PN_IDX_1,TestColumn3=@PN_IDX_2,TestColumn4=@PN_IDX_3,TestColumn5=@PN_IDX_4,TestColumn6=@PN_IDX_5,TestColumn7=@PN_IDX_6,TestColumn8=@PN_IDX_7";
SqlParameter[] expectedParameter = new SqlParameter[8]
{
SqlParameter.InternalCreate(fakeDb, "TestColumn1", 0, entity.Test1),
SqlParameter.InternalCreate(fakeDb, "TestColumn2", 1, entity.Test2),
SqlParameter.InternalCreate(fakeDb, "TestColumn3", 2, entity.Test3),
SqlParameter.InternalCreate(fakeDb, "TestColumn4", 3, entity.Test4),
SqlParameter.InternalCreate(fakeDb, "TestColumn5", 4, DbType.Int32, entity.Test5),
SqlParameter.InternalCreate(fakeDb, "TestColumn6", 5, DbType.Double, entity.Test6),
SqlParameter.InternalCreate(fakeDb, "TestColumn7", 6, DbType.DateTime, entity.Test7),
SqlParameter.InternalCreate(fakeDb, "TestColumn8", 7, DbType.Int16, entity.Test8)
};
UpdateCommand cmd = fakeDb.CreateUpdateCommand(provider.TableName).Set(entity);
String actualSql = cmd.GetCommandText().Trim();
SqlParameter[] actualParameter = cmd.GetAllParameters();
Assert.AreEqual(expectedSql, actualSql);
for (Int32 i = 0; i < actualParameter.Length; i++)
{
Assert.AreEqual(expectedParameter[i], actualParameter[i]);
}
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
internal SqlBasicParameterCondition(AbstractSqlCommand baseCommand, SqlParameter parameterOne, SqlParameter parameterTwo, SqlOperator op)
: base(baseCommand)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms)
{
if (connection.State != ConnectionState.Open) connection.Open();
command.Connection = connection;
command.CommandTimeout = CommandTimeOut;
// 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText;
// 分配事务
if (transaction != null)
{
command.Transaction = transaction;
}
// 设置命令类型.
command.CommandType = commandType;
if (parms != null && parms.Length > 0)
{
//预处理SqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
foreach (SqlParameter parameter in parms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
}
command.Parameters.AddRange(parms);
}
}
public static void SQLBU503165Test()
{
SqlParameter p = new SqlParameter();
p.SqlValue = new DateTime(1200, 1, 1);
p.SqlDbType = SqlDbType.DateTime2;
DateTime expectedValue = new DateTime(1200, 1, 1);
Assert.True(p.Value.Equals(expectedValue), "FAILED: Value did not match expected DateTime value");
Assert.True(p.SqlValue.Equals(expectedValue), "FAILED: SqlValue did not match expected DateTime value");
}
public void Ctor_WithParameterNameAndValue_SetsPropertyValues()
{
var value = Guid.NewGuid();
var parameter = new SqlParameter("Id", value);
Assert.NotNull(parameter.ParameterName);
Assert.Equal("Id", parameter.ParameterName);
Assert.NotNull(parameter.Value);
Assert.Equal(value, parameter.Value);
}
public override bool InsertCourseCategory(ref CourseCategoryInfo entity)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", SqlDbType.Int) { Value = entity.CourseId },
new SqlParameter("@CategoryId", SqlDbType.Int) { Value = entity.CategoryId }
};
return SqlHelper.
}
public static void SQLBU503290Test()
{
using (SqlConnection conn = new SqlConnection(DataTestUtility.TcpConnStr))
{
conn.Open();
SqlParameter p = new SqlParameter("@p", SqlDbType.DateTimeOffset);
p.Value = DBNull.Value;
p.Size = 27;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT @p";
cmd.Parameters.Add(p);
Assert.True(cmd.ExecuteScalar() is DBNull, "FAILED: ExecuteScalar did not return a result of type DBNull");
}
}
public UserInfo CheckUserInfoByUserNameAndPassword(UserInfo info)
{
string sql = "select UserID, FirstName, LastName, UserName, Email, Password, Address, Mobile, HomePhone, WorkPhone, OrderID from UserInfo where [UserName]=@UserName and [Password]=@Password";
SqlParameter[] ps = new SqlParameter[] { new SqlParameter("@UserName", info.UserName), new SqlParameter("@Password", info.Password) };
DataTable table = SqlHelper.GetList(sql, ps);
if ((table != null) && (table.Rows.Count > 0))
{
List<UserInfo> list = new List<UserInfo>();
foreach (DataRow row in table.Rows)
{
list.Add(this.DtataRowToUserInfo(row));
}
if (list.Count > 0)
{
return list[0];
}
}
return null;
}
public void AddParameter(string paramName, object paramValue, SqlDbType paramType)
{
SqlParameter sqlParameter;
if (paramName == null) {
throw new ArgumentNullException("paramName");
}
if (paramName == string.Empty) {
throw new ArgumentException("\"paramName\" cannot be an empty string", "paramName");
}
if (paramValue == null) {
throw new ArgumentNullException("paramValue");
}
sqlParameter = new SqlParameter("@" + paramName, paramType);
sqlParameter.Value = paramValue;
_parameters.Add(sqlParameter);
}
public void LinqInsertTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
TestEntityDataProvider provider = new TestEntityDataProvider(fakeDb);
TestEntity entity = new TestEntity() { Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8 };
String expectedSql = "INSERT INTO TestTable ( TestColumn1,TestColumn2,TestColumn3,TestColumn4,TestColumn5,TestColumn6,TestColumn7,TestColumn8 ) " +
"VALUES ( @PN_IDX_0,@PN_IDX_1,@PN_IDX_2,@PN_IDX_3,@PN_IDX_4,@PN_IDX_5,@PN_IDX_6,@PN_IDX_7 )";
SqlParameter[] expectedParameter = new SqlParameter[8]
{
SqlParameter.InternalCreate(fakeDb, "TestColumn1", 0, entity.Test1),
SqlParameter.InternalCreate(fakeDb, "TestColumn2", 1, entity.Test2),
SqlParameter.InternalCreate(fakeDb, "TestColumn3", 2, entity.Test3),
SqlParameter.InternalCreate(fakeDb, "TestColumn4", 3, entity.Test4),
SqlParameter.InternalCreate(fakeDb, "TestColumn5", 4, DbType.Int32, entity.Test5),
SqlParameter.InternalCreate(fakeDb, "TestColumn6", 5, DbType.Double, entity.Test6),
SqlParameter.InternalCreate(fakeDb, "TestColumn7", 6, DbType.DateTime, entity.Test7),
SqlParameter.InternalCreate(fakeDb, "TestColumn8", 7, DbType.Int16, entity.Test8)
};
InsertCommand cmd = fakeDb.CreateInsertCommand(provider.TableName)
.Add<TestEntity>(c => c.Test1, entity.Test1)
.Add<TestEntity>(c => c.Test2, entity.Test2)
.Add<TestEntity>(c => c.Test3, entity.Test3)
.Add<TestEntity>(c => c.Test4, entity.Test4)
.Add<TestEntity>(c => c.Test5, entity.Test5)
.Add<TestEntity>(c => c.Test6, entity.Test6)
.Add<TestEntity>(c => c.Test7, entity.Test7)
.Add<TestEntity>(c => c.Test8, entity.Test8);
String actualSql = cmd.GetCommandText().Trim();
SqlParameter[] actualParameter = cmd.GetAllParameters();
Assert.AreEqual(expectedSql, actualSql);
for (Int32 i = 0; i < actualParameter.Length; i++)
{
Assert.AreEqual(expectedParameter[i], actualParameter[i]);
}
}
/// <summary>
/// 初始化Sql语句参数类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="columnName">字段名</param>
/// <param name="parameterIndex">参数索引</param>
/// <param name="value">赋值内容</param>
private DataParameter(AbstractDatabase database, String columnName, String parameterIndex, Object value)
{
this._parameter = new SqlParameter();
this._parameter.SourceColumn = columnName;
this._parameter.ParameterName = database.InternalGetParameterName("PN_IDX_" + parameterIndex);
if (value == null)
{
this._parameter.Value = DBNull.Value;
}
else if (value is DateTime)
{
DateTime dt = (DateTime)value;
this._parameter.Value = dt.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
this._parameter.Value = value;
}
this._isUseParameter = true;
}
/// <summary>
/// 初始化Sql查询语句类
/// </summary>
/// <param name="parameterOne">参数一</param>
/// <param name="parameterTwo">参数二</param>
/// <param name="op">条件运算符</param>
internal SqlBasicParameterCondition(SqlParameter parameterOne, SqlParameter parameterTwo, SqlOperator op)
{
this._parameterOne = parameterOne;
this._parameterTwo = parameterTwo;
this._operator = op;
}
/// <summary>
/// 根据id 删除一条数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(int id)
{
string sql = "delete [dbo].[T_Setting] where Id=@id";
SqlParameter pm = new SqlParameter("@id", SqlDbType.Int) { Value = id };
return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pm);
}
public DataTable GetTransformationData(int PageNo, int PageSize, string TableName, string RowField, string SelectedFields,
string WhereCondition, string ClientID, string ProjectID, string SystemID, string selectSecrchOption
)
{
DataSet dsTransformationData = new DataSet();
DataTable dtTransformationData = new DataTable();
try
{
String connectionString = ApplicationCommon.GetConnectionString("BW");
const string sqlCommand = StoredProcedures.SPSelectTransformationDetails;
SqlParameter[] paramCollection = new SqlParameter[10];
paramCollection[0] = new SqlParameter();
paramCollection[0].SqlDbType = SqlDbType.Int;
paramCollection[0].ParameterName = "@PageNo";
paramCollection[0].Value = PageNo;
paramCollection[1] = new SqlParameter();
paramCollection[1].SqlDbType = SqlDbType.Int;
paramCollection[1].ParameterName = "@PageSize";
paramCollection[1].Value = PageSize;
paramCollection[2] = new SqlParameter();
paramCollection[2].SqlDbType = SqlDbType.VarChar;
paramCollection[2].ParameterName = "@TableName";
paramCollection[2].Value = TableName;
paramCollection[3] = new SqlParameter();
paramCollection[3].SqlDbType = SqlDbType.VarChar;
paramCollection[3].ParameterName = "@RowField";
paramCollection[3].Value = RowField;
paramCollection[4] = new SqlParameter();
paramCollection[4].SqlDbType = SqlDbType.VarChar;
paramCollection[4].ParameterName = "@SelectedFields";
paramCollection[4].Value = SelectedFields;
paramCollection[5] = new SqlParameter();
paramCollection[5].SqlDbType = SqlDbType.VarChar;
paramCollection[5].ParameterName = "@WhereCondition";
paramCollection[5].Value = WhereCondition;
paramCollection[6] = new SqlParameter();
paramCollection[6].SqlDbType = SqlDbType.VarChar;
paramCollection[6].ParameterName = "@ClientID";
paramCollection[6].Value = ClientID;
paramCollection[7] = new SqlParameter();
paramCollection[7].SqlDbType = SqlDbType.VarChar;
paramCollection[7].ParameterName = "@ProjectID";
paramCollection[7].Value = ProjectID;
paramCollection[8] = new SqlParameter();
paramCollection[8].SqlDbType = SqlDbType.VarChar;
paramCollection[8].ParameterName = "@SystemID";
paramCollection[8].Value = SystemID;
paramCollection[9] = new SqlParameter();
paramCollection[9].SqlDbType = SqlDbType.VarChar;
paramCollection[9].ParameterName = "@selectSecrchOption";
paramCollection[9].Value = selectSecrchOption;
dsTransformationData = objDA.ExecuteQuery(sqlCommand, CommandType.StoredProcedure, paramCollection, connectionString);
if (dsTransformationData.Tables.Count > 0)
{
dtTransformationData = dsTransformationData.Tables[0];
}
}
catch (Exception ex)
{
errlog.Log("BW", "Error", ex.Message, "GetSolutionOverviewData");
}
return(dtTransformationData);
}
public string Guardar_Formacion(Conexion_Gestion_Formacion Detalle_Formacion, ref SqlConnection SqlCon)
{
string rpta = "";
try
{
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
//SqlCmd.Transaction = SqlTra;
SqlCmd.CommandText = "Gestion.AJ_Formacion";
SqlCmd.CommandType = CommandType.StoredProcedure;
//Comienzo a mandar a la base de datos
SqlParameter ParIdformacion = new SqlParameter();
ParIdformacion.ParameterName = "@Idformacion";
ParIdformacion.SqlDbType = SqlDbType.Int;
ParIdformacion.Direction = ParameterDirection.Output;
SqlCmd.Parameters.Add(ParIdformacion);
SqlParameter ParIdempleado = new SqlParameter();
ParIdempleado.ParameterName = "@Idempleado";
ParIdempleado.SqlDbType = SqlDbType.Int;
ParIdempleado.Value = Detalle_Formacion.Idempleados;
SqlCmd.Parameters.Add(ParIdempleado);
SqlParameter ParCodigoID = new SqlParameter();
ParCodigoID.ParameterName = "@CodigoID";
ParCodigoID.SqlDbType = SqlDbType.VarChar;
ParCodigoID.Size = 50;
ParCodigoID.Value = Detalle_Formacion.CodigoID;
SqlCmd.Parameters.Add(ParCodigoID);
SqlParameter ParEntidad = new SqlParameter();
ParEntidad.ParameterName = "@Entidad";
ParEntidad.SqlDbType = SqlDbType.VarChar;
ParEntidad.Size = 50;
ParEntidad.Value = Detalle_Formacion.Entidad;
SqlCmd.Parameters.Add(ParEntidad);
SqlParameter ParCarrera = new SqlParameter();
ParCarrera.ParameterName = "@Carrera";
ParCarrera.SqlDbType = SqlDbType.VarChar;
ParCarrera.Size = 50;
ParCarrera.Value = Detalle_Formacion.Carrera;
SqlCmd.Parameters.Add(ParCarrera);
SqlParameter ParAño = new SqlParameter();
ParAño.ParameterName = "@Año";
ParAño.SqlDbType = SqlDbType.VarChar;
ParAño.Size = 20;
ParAño.Value = Detalle_Formacion.Año;
SqlCmd.Parameters.Add(ParAño);
//Ejecutamos nuestro comando
rpta = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "NO se Ingreso el Registro";
}
catch (Exception ex)
{
rpta = ex.Message;
}
return rpta;
}
private void OK_Click(object sender, EventArgs e)
{
if (CmbOrg.Text.Trim() == "")
{
MessageBox.Show("Morate odabrati grupu.");
}
else
{
//tamara 21.10.2020.
Program.ID_Jezik = VratiJezik();
//Djora 26.09.20
int standardHeight = 1080; // 600; //900
int standardWidth = 1920; // 800; //1440
int presentHeight = Screen.PrimaryScreen.Bounds.Height; //.Bounds.Height;
int presentWidth = Screen.PrimaryScreen.Bounds.Width;
float heightRatio = (float)((float)presentHeight / (float)standardHeight);
float widthRatio = (float)((float)presentWidth / (float)standardWidth);
//Djora 28.10.21 dodato jer se kod Bojane i ako ima iste parametre za ekran kao ja, 1920x1080 i kod menbe je dobro, a kod nje nije
//moj ekran je ima velicinu 226, a njena 223
//heightRatio = heightRatio + (float)0.1;
float skalFaktor = getScalingFactor();
heightRatio = heightRatio * skalFaktor;
widthRatio = widthRatio * skalFaktor;
Program.RacioWith = (float)widthRatio;
//Djora 17.11.21
if ((presentHeight == 768 && presentWidth == 1366) || (presentHeight == 768 && presentWidth == 1360))
{
Program.RacioHeight = (float)0.80;
}
else
{
Program.RacioHeight = (float)heightRatio;
}
string ImeKorisnika = "";
string PassKorisnika = "";
string n = UsernameTextBox.Text;
lblBaza.Text = "";
lblGrupa.Text = "";
if (n.Length == 0)
{
MessageBox.Show("Polje korisničko ime je prazno.");
return;
}
n = PasswordTextBox.Text;
if (n.Length == 0)
{
MessageBox.Show("Polje za lozinku je prazno.");
return;
}
using (SqlConnection cnn = new SqlConnection(connectionString))
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
string str = " select suser,Pass,ID_KadrovskaEvidencija,SifRadnika from KadrovskaEvidencija WITH (NOLOCK) where SUSER = @username and id_kadrovskaevidencija <> 1 ";
var usernameParam = new SqlParameter("username", SqlDbType.NVarChar)
{
Value = UsernameTextBox.Text.Trim()
};
var cmd = new SqlCommand
{
CommandText = str,
Connection = cnn
};
cmd.Parameters.Add(usernameParam);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
ImeKorisnika = Convert.ToString(rdr[0]);
PassKorisnika = Convert.ToString(rdr[1]);
Program.idkadar = Convert.ToInt32(rdr[2]);
Program.SifRadnika = Convert.ToString(rdr[3]);
}
else
{
MessageBox.Show("Pogrešno korisničko ime.");
lblBaza.Visible = false;
cmbBaze.Visible = false;
lblGrupa.Visible = false;
CmbOrg.Visible = false;
rdr.Close();
cmd.Dispose();
cnn.Close();
return;
}
rdr.Close();
cmd.Dispose();
string strOrgDeo = "select o.ID_OrganizacionaStruktura,o.ID_OrganizacionaStrukturaStablo,os.Naziv ";
strOrgDeo += " from OrganizacionaStruktura as o WITH(NOLOCK) ,organizacionastrukturastablo os WITH(NOLOCK) ";
strOrgDeo += " where o.Naziv = '" + CmbOrg.Text + "' and o.ID_OrganizacionaStrukturaStablo=os.ID_OrganizacionaStrukturaStablo ;";
DataSet ds = new DataSet();
ds = DB.ReturnDS(strOrgDeo);
DataView dv = ds.Tables[0].DefaultView;
Program.imeFirme = dv[0]["Naziv"].ToString();
Program.idOrgDeo = Convert.ToInt32(dv[0]["ID_OrganizacionaStruktura"]);
Program.idFirme = Convert.ToInt32(dv[0]["ID_OrganizacionaStrukturaStablo"]);
Program.NazivOrg = CmbOrg.Text;
cnn.Close();
}
//ivana 26.10.2021. dodala sam ToLower()
if (UsernameTextBox.Text.ToLower() != ImeKorisnika.ToLower())
{
MessageBox.Show("Pogrešno korisničko ime.");
lblBaza.Visible = false;
cmbBaze.Visible = false;
lblGrupa.Visible = false;
CmbOrg.Visible = false;
UsernameTextBox.Text = "";
return;
}
bool result = PasswordTextBox.Text.Equals(PassKorisnika);
if (result == false)
{
MessageBox.Show("Pogrešna lozinka.");
PasswordTextBox.Text = "";
return;
}
Program.imekorisnika = ImeKorisnika;
Program.IntLogovanje = 1;
Hide();
Program.Parent.Text = Program.imeFirme + "-" + Program.imekorisnika;
Close();
int godina = DateTime.Now.Year;
string ssel = " Select DatumPocetkaObrade from ZakljucenjeKnjiga WITH(NOLOCK) "
+ " where GodinaZakljucenja=" + (godina - 1).ToString() + " and id_firma =1 ";
DataBaseBroker dk = new DataBaseBroker();
DataTable tk = new DataTable();
tk = dk.ReturnDataTable(ssel);
if (tk.Rows.Count > 0)
{
Program.kDatum = Convert.ToDateTime(tk.Rows[0]["DatumPocetkaObrade"]);
}
else
{
Program.kDatum = Convert.ToDateTime("01.01." + (godina - 1).ToString());
}
//Luka 14.07.21 Ako stoji min(Godina) onda iako nije nasao id_kadrovskaEvidencija sa flegom = 1 ipak muje rows.count =1 umesto nula
//ssel = " SELECT min(Godina) as god FROM Godine where flag=1 AND ID_KadrovskaEvidencija=@param0";
ssel = " SELECT Godina as god FROM Godine where flag=1 AND ID_KadrovskaEvidencija=@param0";
tk = dk.ParamsQueryDT(ssel, Program.idkadar);
if (tk.Rows.Count == 0)
{
Program.mGodina = 0;
//Djora 17.07.21
Program.mGodina2 = "";
}
else
{
Program.mGodina = Convert.ToInt32(tk.Rows[0]["god"].ToString());
//Djora 14.07.21
clsRazneFunkcije rf = new clsRazneFunkcije();
Program.mGodina2 = rf.fGodine();
}
//Djora 24.08.21 pocetak ---------------------------------------------------------
var MyIni = new clsIniFile(@"\\192.168.1.4\Repozitorijum\ISBankom\XXXX\xxxx.ini");
//Djora 04.11.21
string mm = MyIni.PopValue("LOGOVANJE", "PutanjaPrenosa");
Program.PutanjaPrenosa = mm;
//ivana 13.12.2021.
mm = MyIni.PopValue("LOGOVANJE", "PutanjaKomitenti");
Program.PutanjaKomitenti = mm;
mm = MyIni.PopValue("LOGOVANJE", "LogError");
Program.LogError = mm;
mm = MyIni.PopValue("LOGOVANJE", "IOSI=");
Program.IOSI = mm;
//Uzima po defoltu
mm = MyIni.PopValue("LOGOVANJE", "FontVelicina");
Program.FontVelicina = Int16.Parse(mm);
mm = MyIni.PopValue("LOGOVANJE", "FontStil");
Program.FontStil = mm;
mm = MyIni.PopValue("LOGOVANJE", "FontColor");
Program.FontColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "BackgroundColor");
Program.BackgroundColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "OddTableRowColor");
Program.OddTableRowColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "EvenTableRowColor");
Program.EvenTableRowColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "ControlBackColor");
Program.ControlBackColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "GridColor");
Program.GridColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "TableBackColor");
Program.TableBackColor = mm;
mm = MyIni.PopValue("LOGOVANJE", "TableHeaderColor");
Program.TableHeaderColor = mm;
//Djora 02.12.21
mm = MyIni.PopValue("LOGOVANJE", "RequiredColor");
Program.RequiredColor = mm;
//Uzima za korisnika ako ima
mm = MyIni.PopValue(Program.imekorisnika, "FontVelicina");
if (mm != "")
{
Program.FontVelicina = Int16.Parse(mm);
}
mm = MyIni.PopValue(Program.imekorisnika, "FontStil");
if (mm != "")
{
Program.FontStil = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "FontColor");
if (mm != "")
{
Program.FontColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "BackgroundColor");
if (mm != "")
{
Program.BackgroundColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "OddTableRowColor");
if (mm != "")
{
Program.OddTableRowColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "EvenTableRowColor");
if (mm != "")
{
Program.EvenTableRowColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "ControlBackColor");
if (mm != "")
{
Program.ControlBackColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "GridColor");
if (mm != "")
{
Program.GridColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "TableBackColor");
if (mm != "")
{
Program.TableBackColor = mm;
}
mm = MyIni.PopValue(Program.imekorisnika, "TableHeaderColor");
if (mm != "")
{
Program.TableHeaderColor = mm;
}
//Djora 24.08.21 kraj ------------------------------------------------------------
//Djora 02.12.21
mm = MyIni.PopValue(Program.imekorisnika, "RequiredColor");
if (mm != "")
{
Program.RequiredColor = mm;
}
}
}
public int UpdateInfoByOrderId(ProductOrderInfo info)
{
string sql = "update [ProductOrder] set IsSend=1 where OrderID=@orderId";
SqlParameter[] ps = new SqlParameter[] { new SqlParameter("@orderId", info.OrderID) };
return SqlHelper.ExecuteNonQuery(sql, ps);
}
/// <summary>
/// Создает новый SqlParameter
/// </summary>
/// <param name="Name"></param>
/// <param name="Value"></param>
/// <param name="Type"></param>
/// <returns></returns>
private SqlParameter CreateParameter(string Name, string Value, SqlDbType Type, int Size , ParameterDirection Direction, bool IsNullable)
{
SqlParameter sp = new SqlParameter();
sp.Direction = Direction;
sp.IsNullable = IsNullable;
sp.SqlDbType = Type;
sp.Size = Size;
sp.ParameterName = Name;
sp.SqlValue = Value;
return sp;
}
protected void btnCreate_Click1(object sender, EventArgs e)
{
//Gets values and passes it to variables
List<String> CheckList = new List<String>();
string pass = txtPassword.Text;
string pass2 = txtPasswordReenter.Text;
string userName = txtUserName.Text;
string address = txtAddress.Text;
string emailAddress = txtNewEmail.Text;
string firstName = txtFirstName.Text;
string lastName = txtLastName.Text;
string billingAddress = txtBillingAddress.Text;
string phoneNumber = txtPhoneNumber.Text;
string securityAnswer1 = txtSQ1Answer.Text;
string securityAnswer2 = txtSQ2Answer.Text;
string securityAnswer3 = txtSQ3Answer.Text;
string securityQuestion1 = ddlSQ1.SelectedValue;
string securityQuestion2 = ddlSQ2.SelectedValue;
string securityQuestion3 = ddlSQ3.SelectedValue;
//Validation checks
int check = 0;
CheckList.Add(userName);
CheckList.Add(address);
CheckList.Add(emailAddress);
CheckList.Add(firstName);
CheckList.Add(lastName);
CheckList.Add(billingAddress);
CheckList.Add(phoneNumber);
CheckList.Add(securityAnswer1);
CheckList.Add(securityAnswer2);
CheckList.Add(securityAnswer3);
CheckList.Add(securityQuestion1);
CheckList.Add(securityQuestion2);
CheckList.Add(securityQuestion3);
CheckList.Add(pass);
CheckList.Add(pass2);
for (int i = 0; i < CheckList.Count; i++)
{
if (CheckList[i] != "")
{
check = check + 1;
}
}
//If validation passes, if passwords are accurate
if (check == 15)
{
if (pass == pass2)
{
lblPassError.Visible = false;
lblPassError1.Visible = false;
lblPassword.Visible = true;
lblPassword1.Visible = true;
//Checks to see if email exists
SqlCommand sqlCommand3 = new SqlCommand();
sqlCommand3.CommandType = CommandType.StoredProcedure;
sqlCommand3.CommandText = "TP_SelectUserIDEmailCreateUser";
SqlParameter EmailAddress = new SqlParameter("@Email", txtNewEmail.Text);
EmailAddress.Direction = ParameterDirection.Input;
sqlCommand3.Parameters.Add(EmailAddress);
DataSet ds = db.GetDataSetUsingCmdObj(sqlCommand3);
int size = ds.Tables[0].Rows.Count;
//If email doesn't exist
if (size == 0)
{
//Adds all values to a soap object
FitnessService.User newUsers = new FitnessService.User();
Users user = new Users();
newUsers.FirstName = firstName;
newUsers.LastName = lastName;
newUsers.EmailAddress = emailAddress;
newUsers.UserName = userName;
newUsers.BillingAddress = billingAddress;
newUsers.SecurityQuestion1 = securityQuestion1;
newUsers.SecurityQuestion2 = securityQuestion2;
newUsers.SecurityQuestion3 = securityQuestion3;
newUsers.SecurityAnswer1 = securityAnswer1;
newUsers.SecurityAnswer2 = securityAnswer2;
newUsers.SecurityAnswer3 = securityAnswer3;
newUsers.Password = pass;
newUsers.Type = "Admin";
newUsers.Experience = ddlImage.SelectedValue;
newUsers.UserImage = ddlImage.SelectedValue;
newUsers.DateCreated = DateTime.Now.ToString();
user.BinaryPassword = txtPassword.Text;
user.BinaryAddress = txtBillingAddress.Text;
arrayNewUser.Add(newUsers);
//Executes soap
Boolean test = pxy.AddUser(newUsers);
//Gets UserID from newly created account
SqlCommand sqlCommand3B = new SqlCommand();
sqlCommand3B.CommandType = CommandType.StoredProcedure;
sqlCommand3B.CommandText = "TP_SelectUserIDEmailCreateUser";
SqlParameter EmailAddress1 = new SqlParameter("@Email", txtNewEmail.Text);
EmailAddress1.Direction = ParameterDirection.Input;
sqlCommand3B.Parameters.Add(EmailAddress1);
DataSet ds2 = db.GetDataSetUsingCmdObj(sqlCommand3B);
//Assigns UserID value to int variable
int userId = Convert.ToInt32(ds2.Tables[0].Rows[0]["UserID"]);
//Serializes object containing password and address
BinaryFormatter serializer = new BinaryFormatter();
MemoryStream memStream = new MemoryStream();
Byte[] byteArray;
serializer.Serialize(memStream, user);
byteArray = memStream.ToArray();
//Inserts serialized object to database
SqlCommand sqlCommand3A = new SqlCommand();
sqlCommand3A.CommandType = CommandType.StoredProcedure;
sqlCommand3A.CommandText = "TP_UpdateUsersCreateBinary";
SqlParameter ID = new SqlParameter("@ID", userId);
ID.Direction = ParameterDirection.Input;
sqlCommand3A.Parameters.Add(ID);
SqlParameter objectBinary = new SqlParameter("@BinaryObject", byteArray);
objectBinary.Direction = ParameterDirection.Input;
sqlCommand3A.Parameters.Add(objectBinary);
db.DoUpdateUsingCmdObj(sqlCommand3A);
//Creates Inbox tag for user
SqlCommand sqlCommand4A = new SqlCommand();
sqlCommand4A.CommandType = CommandType.StoredProcedure;
sqlCommand4A.CommandText = "TP_InsertIntoTags";
SqlParameter UserID2 = new SqlParameter("@ID", userId);
UserID2.Direction = ParameterDirection.Input;
sqlCommand4A.Parameters.Add(UserID2);
SqlParameter TagName = new SqlParameter("@TagName", "Inbox");
TagName.Direction = ParameterDirection.Input;
sqlCommand4A.Parameters.Add(TagName);
db.DoUpdateUsingCmdObj(sqlCommand4A);
//Creates Sent tag for user
SqlCommand sqlCommand5A = new SqlCommand();
sqlCommand5A.CommandType = CommandType.StoredProcedure;
sqlCommand5A.CommandText = "TP_InsertIntoTags";
SqlParameter UserID3 = new SqlParameter("@ID", userId);
UserID3.Direction = ParameterDirection.Input;
sqlCommand5A.Parameters.Add(UserID3);
SqlParameter Sent = new SqlParameter("@TagName", "Sent");
Sent.Direction = ParameterDirection.Input;
sqlCommand5A.Parameters.Add(Sent);
db.DoUpdateUsingCmdObj(sqlCommand5A);
Response.Redirect("LogIn.aspx");
}
else
{
Response.Write("<script>alert('The EmailAddress is already taken! Please Try Again!') </script>");
}
}
else
{
lblPassError.Visible = true;
lblPassError1.Visible = true;
lblPassword.Visible = false;
lblPassword1.Visible = false;
}
}
else
{
Response.Write("<script>alert('Every Field Is Needed To Make An Account Dummy!') </script>");
}
}
internal SqlParameterInfo(SqlParameter parameter) {
this.parameter = parameter;
}
private void ProcessCommand(DbCommand cmd)
{
object total;
if (DC.Database.IsMySql())
{
List<MySqlParameter> parms = new List<MySqlParameter>();
foreach (MySqlParameter item in cmd.Parameters)
{
parms.Add(new MySqlParameter(string.Format("@{0}", item.ParameterName), item.Value));
}
if (cmd.CommandType == CommandType.StoredProcedure)
{
parms.Add(new MySqlParameter("@SearchMode", Enum.GetName(typeof(ListVMSearchModeEnum), SearcherMode)));
parms.Add(new MySqlParameter("@NeedPage", (NeedPage && Searcher.Limit != -1)));
parms.Add(new MySqlParameter("@CurrentPage", Searcher.Page));
parms.Add(new MySqlParameter("@RecordsPerPage", Searcher.Limit));
parms.Add(new MySqlParameter("@Sort", Searcher.SortInfo?.Property));
parms.Add(new MySqlParameter("@SortDir", Searcher.SortInfo?.Direction));
parms.Add(new MySqlParameter("@IDs", Ids == null ? "" : Ids.ToSpratedString()));
MySqlParameter outp = new MySqlParameter("@TotalRecords", MySqlDbType.Int64)
{
Value = 0,
Direction = ParameterDirection.Output
};
parms.Add(outp);
}
var pa = parms.ToArray();
EntityList = DC.Run<TModel>(cmd.CommandText, cmd.CommandType, pa).ToList();
if (cmd.CommandType == CommandType.StoredProcedure)
{
total = pa.Last().Value;
}
else
{
total = EntityList.Count;
}
}
else if (DC.Database.IsNpgsql())
{
List<NpgsqlParameter> parms = new List<NpgsqlParameter>();
foreach (NpgsqlParameter item in cmd.Parameters)
{
parms.Add(new NpgsqlParameter(string.Format("@{0}", item.ParameterName), item.Value));
}
if (cmd.CommandType == CommandType.StoredProcedure)
{
parms.Add(new NpgsqlParameter("@SearchMode", Enum.GetName(typeof(ListVMSearchModeEnum), SearcherMode)));
parms.Add(new NpgsqlParameter("@NeedPage", (NeedPage && Searcher.Limit != -1)));
parms.Add(new NpgsqlParameter("@CurrentPage", Searcher.Page));
parms.Add(new NpgsqlParameter("@RecordsPerPage", Searcher.Limit));
parms.Add(new NpgsqlParameter("@Sort", Searcher.SortInfo?.Property));
parms.Add(new NpgsqlParameter("@SortDir", Searcher.SortInfo?.Direction));
parms.Add(new NpgsqlParameter("@IDs", Ids == null ? "" : Ids.ToSpratedString()));
NpgsqlParameter outp = new NpgsqlParameter("@TotalRecords", NpgsqlDbType.Bigint)
{
Value = 0,
Direction = ParameterDirection.Output
};
parms.Add(outp);
}
var pa = parms.ToArray();
EntityList = DC.Run<TModel>(cmd.CommandText, cmd.CommandType, pa).ToList();
if (cmd.CommandType == CommandType.StoredProcedure)
{
total = pa.Last().Value;
}
else
{
total = EntityList.Count;
}
}
else
{
List<SqlParameter> parms = new List<SqlParameter>();
foreach (SqlParameter item in cmd.Parameters)
{
parms.Add(new SqlParameter(string.Format("@{0}", item.ParameterName), item.Value));
}
if (cmd.CommandType == CommandType.StoredProcedure)
{
parms.Add(new SqlParameter("@SearchMode", Enum.GetName(typeof(ListVMSearchModeEnum), SearcherMode)));
parms.Add(new SqlParameter("@NeedPage", (NeedPage && Searcher.Limit != -1)));
parms.Add(new SqlParameter("@CurrentPage", Searcher.Page));
parms.Add(new SqlParameter("@RecordsPerPage", Searcher.Limit));
parms.Add(new SqlParameter("@Sort", Searcher.SortInfo?.Property));
parms.Add(new SqlParameter("@SortDir", Searcher.SortInfo?.Direction));
parms.Add(new SqlParameter("@IDs", Ids == null ? "" : Ids.ToSpratedString()));
SqlParameter outp = new SqlParameter("@TotalRecords", 0)
{
Direction = ParameterDirection.Output
};
parms.Add(outp);
}
var pa = parms.ToArray();
EntityList = DC.Run<TModel>(cmd.CommandText, cmd.CommandType, pa).ToList();
if (cmd.CommandType == CommandType.StoredProcedure)
{
total = pa.Last().Value;
}
else
{
total = EntityList.Count;
}
}
if (NeedPage && Searcher.Limit != -1)
{
if (total != null)
{
try
{
Searcher.Count = long.Parse(total.ToString());
Searcher.PageCount = (int)((Searcher.Count - 1) / Searcher.Limit + 1);
}
catch { }
}
}
else
{
Searcher.PageCount = EntityList.Count;
}
}
protected void CreateUser_Click(object sender, EventArgs e)
{
//get the new user name and password from the text boxes
string email = Email.Text;
string username = UserName.Text;
string password = Password.Text;
//empty out the textboxes after we get the data from them
Email.Text = "";
UserName.Text = "";
Password.Text = "";
//1. create a connection to the database
SqlConnection dbConn = new SqlConnection();
//1.1 set the connection string to a specific database
dbConn.ConnectionString = ConfigurationManager.ConnectionStrings["MealsProject"].ConnectionString;
//2. create a command to tell the db what to do
SqlCommand command = new SqlCommand();
//2.1 set the text of the query to be sent with the command
command.CommandText = "INSERT INTO Users(username, password, email) VALUES (@username, @password, @email)";
//this will be a plain text query not a stored proc
command.CommandType = System.Data.CommandType.Text;
//associate the command with a connection
command.Connection = dbConn;
//2.2 fill in the parameters from the query
SqlParameter userNameParam = new SqlParameter();
//the text that is being replaced in the query
userNameParam.ParameterName = "@username";
//the value to replace it with
userNameParam.Value = username;
//the type and size of the data
userNameParam.SqlDbType = System.Data.SqlDbType.NVarChar;
userNameParam.Size = 50;
SqlParameter emailParam = new SqlParameter();
emailParam.ParameterName = "@email";
emailParam.Value = email;
emailParam.SqlDbType = System.Data.SqlDbType.NVarChar;
emailParam.Size = 50;
SqlParameter passwordParam = new SqlParameter();
//the text that is being replaced in the query
passwordParam.ParameterName = "@password";
//the value to replace it with
passwordParam.Value = password;
//the type and size of the data
passwordParam.SqlDbType = System.Data.SqlDbType.NVarChar;
passwordParam.Size = 50;
//add the parameters to the command
command.Parameters.Add(emailParam);
command.Parameters.Add(userNameParam);
command.Parameters.Add(passwordParam);
//3. open the connection to allow the command to go through it
dbConn.Open();
//4. execute the query (there are no results in an insert, use the non-query version)
command.ExecuteNonQuery();
//5. clean up all the resources
command.Dispose();
dbConn.Close();
dbConn.Dispose();
}
public static DataTable QueryOrderForm(string formn, DateTime dBegin, DateTime dEnd)
{
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@bdate", dBegin), new SqlParameter("@edate", dEnd) };
return publicDbOpClass.ExecuteDataTable("p_rep_QueryForm" + formn, commandParameters);
}
public static DataTable QueryOrderForm4(string contactCode, string contactName, string formCode, string contractCode, string ckCode, string resourceType, string resourceName, string standard, string pinpai, string moneytype, string prjCode, string prjName, DateTime dBegin, DateTime dEnd)
{
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@contactCode", contactCode), new SqlParameter("@contactName", contactName), new SqlParameter("@formCode", formCode), new SqlParameter("@contractCode", contractCode), new SqlParameter("@ckCode", ckCode), new SqlParameter("@resourceType", resourceType), new SqlParameter("@resourceName", resourceName), new SqlParameter("@standard", standard), new SqlParameter("@pinpai", pinpai), new SqlParameter("@moneytype", moneytype), new SqlParameter("@prjCode", prjCode), new SqlParameter("@prjName", prjName), new SqlParameter("@bdate", dBegin.ToShortDateString()), new SqlParameter("@edate", dEnd.ToShortDateString()) };
return publicDbOpClass.ExecuteDataTable("p_rep_QueryForm4", commandParameters);
}
public static DataTable QueryOrderForm2(string contactCode, string contactName, string formCode, string moneytype, DateTime dBegin, DateTime dEnd)
{
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@contactCode", contactCode), new SqlParameter("@contactName", contactName), new SqlParameter("@formCode", formCode), new SqlParameter("@moneytype", moneytype), new SqlParameter("@bdate", dBegin.ToShortDateString()), new SqlParameter("@edate", dEnd.ToShortDateString()) };
return publicDbOpClass.ExecuteDataTable("p_rep_QueryForm2", commandParameters);
}
/// <summary>
/// 创建新的Sql语句参数类
/// </summary>
/// <param name="database">数据库</param>
/// <param name="columnName">字段名</param>
/// <param name="parameterIndex">参数索引</param>
/// <param name="dbType">字段类型</param>
/// <param name="value">赋值内容</param>
/// <returns>Sql语句参数类</returns>
internal static SqlParameter InternalCreate(AbstractDatabase database, String columnName, Int32 parameterIndex, DbType dbType, Object value)
{
SqlParameter param = new SqlParameter(database, columnName, parameterIndex, value);
param._parameter.DbType = dbType;
return param;
}
public static void guardarPedido_base(string strXML, int car_id, string codSucursal, string pTipo, string pMensajeEnFactura, string pMensajeEnRemito, string pTipoEnvio, bool pIsUrgente)
{
SqlConnection Conn = new SqlConnection(accesoBD.ObtenerConexión());
SqlCommand cmdComandoInicio = new SqlCommand("CAR.spCargarPedido", Conn);
cmdComandoInicio.CommandType = CommandType.StoredProcedure;
SqlParameter paLrc_id = cmdComandoInicio.Parameters.Add("@car_id", SqlDbType.Int);
SqlParameter paLrc_codSucursal = cmdComandoInicio.Parameters.Add("@car_codSucursal", SqlDbType.NVarChar, 2);
SqlParameter palrc_codCliente = cmdComandoInicio.Parameters.Add("@car_codCliente", SqlDbType.Int);
// SqlParameter paCodTransfer = cmdComandoInicio.Parameters.Add("@codTransfer", SqlDbType.Int);
SqlParameter paTipo = cmdComandoInicio.Parameters.Add("@tipo", SqlDbType.NVarChar, 100);
SqlParameter paFechaPedido = cmdComandoInicio.Parameters.Add("@FechaPedido", SqlDbType.DateTime);
SqlParameter paMensajeEnFactura = cmdComandoInicio.Parameters.Add("@MensajeEnFactura", SqlDbType.NVarChar, -1);
SqlParameter paMensajeEnRemito = cmdComandoInicio.Parameters.Add("@MensajeEnRemito", SqlDbType.NVarChar, -1);
SqlParameter paTipoEnvio = cmdComandoInicio.Parameters.Add("@TipoEnvio", SqlDbType.NVarChar, -1);
SqlParameter paIsUrgente = cmdComandoInicio.Parameters.Add("@IsUrgente", SqlDbType.Bit);
SqlParameter paStrXML = cmdComandoInicio.Parameters.Add("@strXML", SqlDbType.Xml);
paLrc_id.Value = car_id;
paLrc_codSucursal.Value = codSucursal;
palrc_codCliente.Value = (int)((capaDatos.Usuario)HttpContext.Current.Session["clientesDefault_Usuario"]).usu_codCliente;
paFechaPedido.Value = DateTime.Now;
if (pMensajeEnFactura == null)
{
paMensajeEnFactura.Value = DBNull.Value;
}
else
{
paMensajeEnFactura.Value = pMensajeEnFactura;
}
if (pMensajeEnRemito == null)
{
paMensajeEnRemito.Value = DBNull.Value;
}
else
{
paMensajeEnRemito.Value = pMensajeEnRemito;
}
if (pTipoEnvio == null)
{
paTipoEnvio.Value = DBNull.Value;
}
else
{
paTipoEnvio.Value = pTipoEnvio;
}
paIsUrgente.Value = pIsUrgente;
//paCodTransfer.Value = DBNull.Value;
paTipo.Value = pTipo;
paStrXML.Value = strXML;
try
{
Conn.Open();
cmdComandoInicio.ExecuteNonQuery();
}
catch (Exception ex)
{
//return -1;
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public Esito AggiornaAzienda(Anag_Clienti_Fornitori azienda, Anag_Utenti utente)
{
Esito esito = new Esito();
try
{
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlConstr))
{
using (System.Data.SqlClient.SqlCommand StoreProc = new System.Data.SqlClient.SqlCommand("UpdateClienteFornitore"))
{
using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
{
StoreProc.Connection = con;
sda.SelectCommand = StoreProc;
StoreProc.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter id = new System.Data.SqlClient.SqlParameter("@id", azienda.Id);
id.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(id);
// PARAMETRI PER LOG UTENTE
SqlParameter idUtente = new SqlParameter("@idUtente", utente.id);
idUtente.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(idUtente);
SqlParameter nomeUtente = new SqlParameter("@nomeUtente", utente.username);
nomeUtente.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(nomeUtente);
// FINE PARAMETRI PER LOG UTENTE
SqlParameter attivo = new SqlParameter("@attivo", azienda.Attivo);
attivo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(attivo);
SqlParameter capLegale = new SqlParameter("@capLegale", azienda.CapLegale);
capLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(capLegale);
SqlParameter capOperativo = new SqlParameter("@capOperativo", azienda.CapOperativo);
capOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(capOperativo);
SqlParameter cliente = new SqlParameter("@cliente", azienda.Cliente);
cliente.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(cliente);
SqlParameter codiceFiscale = new SqlParameter("@codiceFiscale", azienda.CodiceFiscale);
codiceFiscale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(codiceFiscale);
SqlParameter codiceIdentificativo = new SqlParameter("@codiceIdentificativo", azienda.CodiceIdentificativo);
codiceIdentificativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(codiceIdentificativo);
SqlParameter comuneLegale = new SqlParameter("@comuneLegale", azienda.ComuneLegale);
comuneLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(comuneLegale);
SqlParameter comuneOperativo = new SqlParameter("@comuneOperativo", azienda.ComuneOperativo);
comuneOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(comuneOperativo);
SqlParameter email = new SqlParameter("@email", azienda.Email);
email.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(email);
SqlParameter fax = new SqlParameter("@fax", azienda.Fax);
fax.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(fax);
SqlParameter fornitore = new SqlParameter("@fornitore", azienda.Fornitore);
fornitore.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(fornitore);
SqlParameter iban = new SqlParameter("@iban", azienda.Iban);
iban.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(iban);
SqlParameter indirizzoLegale = new SqlParameter("@indirizzoLegale", azienda.IndirizzoLegale);
indirizzoLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(indirizzoLegale);
SqlParameter indirizzoOperativo = new SqlParameter("@indirizzoOperativo", azienda.IndirizzoOperativo);
indirizzoOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(indirizzoOperativo);
SqlParameter nazioneLegale = new SqlParameter("@nazioneLegale", azienda.NazioneLegale);
nazioneLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(nazioneLegale);
SqlParameter nazioneOperativo = new SqlParameter("@nazioneOperativo", azienda.NazioneOperativo);
nazioneOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(nazioneOperativo);
SqlParameter note = new SqlParameter("@note", azienda.Note);
note.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(note);
SqlParameter numeroCivicoLegale = new SqlParameter("@numeroCivicoLegale", azienda.NumeroCivicoLegale);
numeroCivicoLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(numeroCivicoLegale);
SqlParameter numeroCivicoOperativo = new SqlParameter("@numeroCivicoOperativo", azienda.NumeroCivicoOperativo);
numeroCivicoOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(numeroCivicoOperativo);
SqlParameter pagamento = new SqlParameter("@pagamento", azienda.Pagamento);
pagamento.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(pagamento);
SqlParameter partitaIva = new SqlParameter("@partitaIva", azienda.PartitaIva);
partitaIva.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(partitaIva);
SqlParameter pec = new SqlParameter("@pec", azienda.Pec);
pec.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(pec);
SqlParameter provinciaLegale = new SqlParameter("@provinciaLegale", azienda.ProvinciaLegale);
provinciaLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(provinciaLegale);
SqlParameter provinciaOperativo = new SqlParameter("@provinciaOperativo", azienda.ProvinciaOperativo);
provinciaOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(provinciaOperativo);
SqlParameter ragioneSociale = new SqlParameter("@ragioneSociale", azienda.RagioneSociale);
ragioneSociale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(ragioneSociale);
SqlParameter telefono = new SqlParameter("@telefono", azienda.Telefono);
telefono.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(telefono);
SqlParameter tipo = new SqlParameter("@tipo", azienda.Tipo);
tipo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(tipo);
SqlParameter tipoIndirizzoLegale = new SqlParameter("@tipoIndirizzoLegale", azienda.TipoIndirizzoLegale);
tipoIndirizzoLegale.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(tipoIndirizzoLegale);
SqlParameter tipoIndirizzoOperativo = new SqlParameter("@tipoIndirizzoOperativo", azienda.TipoIndirizzoOperativo);
tipoIndirizzoOperativo.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(tipoIndirizzoOperativo);
SqlParameter webSite = new SqlParameter("@webSite", azienda.WebSite);
webSite.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(webSite);
SqlParameter notaPagamento = new SqlParameter("@notaPagamento", azienda.NotaPagamento);
notaPagamento.Direction = ParameterDirection.Input;
StoreProc.Parameters.Add(notaPagamento);
StoreProc.Connection.Open();
int iReturn = StoreProc.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
esito.Codice = Esito.ESITO_KO_ERRORE_SCRITTURA_TABELLA;
esito.Descrizione = "Anag_Clienti_Fornitori_DAL.cs - aggiornaAzienda " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace;
}
return(esito);
}
private void BackgroundWorker_DoWork_Step4_RunScript(object sender, DoWorkEventArgs e)
{
try
{
var index = e.Argument.ToString();
e.Result = index;
//here code that he need doing
//Step 1
SqlConnection myConnection = default(SqlConnection);
myConnection = new SqlConnection(cs_TestServer);
SqlCommand myCommand = default(SqlCommand);
myCommand = new SqlCommand("[dbo].[sp_SiteReview] @Client,@Allow_Week_Password_Check,@debug,@Display", myConnection);
SqlParameter uClient = new SqlParameter("@Client", SqlDbType.NVarChar, -1);
SqlParameter uAllow_Week_Password_Check = new SqlParameter("@Allow_Week_Password_Check", SqlDbType.Bit);
SqlParameter uDebug = new SqlParameter("@debug", SqlDbType.Bit);
SqlParameter uDisplay = new SqlParameter("@Display", SqlDbType.Bit);
uClient.Value = Client;
uAllow_Week_Password_Check.Value = Allow_Week_Password_Check;
uDebug.Value = Debug;
uDisplay.Value = Display;
myCommand.Parameters.Add(uClient);
myCommand.Parameters.Add(uAllow_Week_Password_Check);
myCommand.Parameters.Add(uDebug);
myCommand.Parameters.Add(uDisplay);
myCommand.CommandTimeout = 0;
//( @Client NVARCHAR(255) = N'General Client',@Allow_Week_Password_Check BIT = 0,@debug BIT = 0,@Display BIT = 0)
Dictionary_Cmd.TryAdd(myCommand.CommandText, myCommand);
myCommand.Connection.Open();
var adapter = new SqlDataAdapter(myCommand);
myConnection.InfoMessage += (Sender, args) =>
{
for (var i = 0; i < args.Errors.Count; i++)
{
if (args.Errors[i].Number > 0)
{
MessageOutput += "\n" + args.Message + "\n";
}
else
{
MessageOutput += args.Message + "\n";
}
}
};
adapter.Fill(XML_dataSet);
strXML = XML_dataSet.Tables[0].Rows[0].ItemArray[0].ToString() ;
// SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (myConnection.State == ConnectionState.Open)
{
myConnection.Dispose();
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
MessageOutput = MessageOutput + "\n" + "Exception:" + ex.Message;
e.Cancel = true;
}
}
internal SqlParameterInfo(SqlParameter parameter, object value) {
this.parameter = parameter;
this.value = value;
}
private void buttonGuardar_Click(object sender, EventArgs e)
{
limpiarErrores();
DateTime Result;
int parsedValue;
DateTimeFormatInfo info = new DateTimeFormatInfo ( );
CultureInfo culture;
culture = CultureInfo.CreateSpecificCulture ( "en-US" );
info.ShortDatePattern = "dd/MM/yyyy";
Regex expEmail = new Regex(@"^([\w.-]+)@([\w-]+)((.(\w){2,3})+)$");
if ( !(DateTime.TryParse ( textBoxBirthday.Text.Trim(), info, DateTimeStyles.None, out Result )) )
{
lblErrorDateFormat.Visible = true;
lblErrorBirthday.Visible = true;
}
else if (!int.TryParse(textBoxDocument.Text.Trim(), out parsedValue))
{
lblErrorNumberValue.Visible = true;
lblErrorDocument.Visible = true;
}
else if (listBoxHoteles.Items.Count == 0)
{
lbllistBoxNoItem.Visible = true;
lblErrorHotel.Visible = true;
}
else if ((DateTime.Compare(DataBase.fechaSistema(), DateTime.Parse(textBoxBirthday.Text.Trim())))<=0)
{
lblErrorBirthday.Visible = true;
lblBirthdayPost.Visible = true;
}
else if (listBoxRoles.Items.Count == 0)
{
lbllistBoxNoItem.Visible = true;
lblErrorRole.Visible = true;
}
else if (!int.TryParse(textBoxBlockNumber.Text.Trim(), out parsedValue))
{
lblErrorNumberValue.Visible = true;
lblErrorBlockNumber.Visible = true;
}
else if (textBoxUserName.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorNoName.Visible = true;
}
else if (textBoxPW.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorPW.Visible = true;
}
else if (textBoxMail.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorMail.Visible = true;
}
else if (!expEmail.IsMatch(textBoxMail.Text))
{
MessageBox.Show("Formato de mail ingresado incorrecto", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
lblErrorMail.Visible = true;
}
else if (textBoxBlock.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorBlock.Visible = true;
}
else if (textBoxBlockNumber.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorBlockNumber.Visible = true;
}
else if (textBoxUser.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorUser.Visible = true;
}
else if (textBoxSurname.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorNoSurname.Visible = true;
}
else if (textBoxBirthday.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorBirthday.Visible = true;
}
else if (textBoxDocument.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorDocument.Visible = true;
}
else if (textBoxDocumentType.Text == "")
{
lblErrorNoField.Visible = true;
lblErrorDocumentType.Visible = true;
}
else
{
string usuarioExistente = string.Format("SELECT COUNT(*) FROM CAIA_UNLIMITED.Usuario WHERE usur_username = '******'", textBoxUser.Text.Trim());
SqlConnection connection = DataBase.conectarBD();
SqlCommand commUser = new SqlCommand(usuarioExistente, connection);
Int32 countUsuarios = Convert.ToInt32(commUser.ExecuteScalar());
if (countUsuarios > 0)
{
connection.Close();
MessageBox.Show("Ya existe un nombre de usuario con el nombre indicado. Intente con otro nombre.");
lblErrorUser.Visible = true;
}
else
{
RolitiesCollection rc = new RolitiesCollection();
foreach (string rol in listBoxRoles.Items)
{
for (int i = 0; i < this._dsRoles.Tables[0].Rows.Count; i++)
{
if (((string)(_dsRoles.Tables[0].Rows[i]["rol_nombre"])) == rol)
{
rc.Add(new Rolities { Roles = ((decimal)(this._dsRoles.Tables[0].Rows[i]["rol_codigo"])) });
}
}
}
HotelitiesCollection hc = new HotelitiesCollection();
foreach (string hotel in listBoxHoteles.Items)
{
for (int i = 0; i < this._dsHoteles.Tables[0].Rows.Count; i++)
{
if (((string)(_dsHoteles.Tables[0].Rows[i]["Hotel"])) == hotel)
{
hc.Add(new Hotelities { Hoteles = ((decimal)(this._dsHoteles.Tables[0].Rows[i]["idHotel"])) });
}
}
}
try
{
SqlConnection createConnection = DataBase.conectarBD();
SqlCommand insertCommand = new SqlCommand("[CAIA_UNLIMITED].sp_CrearUsuarios", createConnection);
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.AddWithValue("@username", textBoxUser.Text.Trim());
insertCommand.Parameters.AddWithValue("@password", encryptPassword(textBoxPW.Text.Trim()));
insertCommand.Parameters.AddWithValue("@name", textBoxUserName.Text.Trim());
insertCommand.Parameters.AddWithValue("@apellido", textBoxSurname.Text.Trim());
insertCommand.Parameters.AddWithValue("@nacionalidad", DBNull.Value);
insertCommand.Parameters.AddWithValue("@tipoDocumento", textBoxDocumentType.Text.Trim());
insertCommand.Parameters.AddWithValue("@documento", Decimal.Parse(textBoxDocument.Text.Trim()));
insertCommand.Parameters.AddWithValue("@fechaNacimiento", DateTime.Parse(textBoxBirthday.Text.Trim()));
if (textBoxCountry.Text.Trim() == "")
{
insertCommand.Parameters.AddWithValue("@pais", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@pais", textBoxCountry.Text.Trim());
}
if (textBoxCity.Text.Trim() == "")
{
insertCommand.Parameters.AddWithValue("@ciudad", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@ciudad", textBoxCity.Text.Trim());
}
insertCommand.Parameters.AddWithValue("@calle", textBoxBlock.Text.Trim());
insertCommand.Parameters.AddWithValue("@numeroCalle", textBoxBlockNumber.Text.Trim());
if (textBoxPiso.Text.Trim() == "")
{
insertCommand.Parameters.AddWithValue("@piso", DBNull.Value);
}
else
{
if (!int.TryParse(textBoxPiso.Text.Trim(), out parsedValue))
{
lblErrorNumberValue.Visible = true;
lblErrorPiso.Visible = true;
return;
}
insertCommand.Parameters.AddWithValue("@piso", Decimal.Parse(textBoxPiso.Text.Trim()));
}
if (textBoxDepartamento.Text.Trim() == "")
{
insertCommand.Parameters.AddWithValue("@departamento", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@departamento", textBoxDepartamento.Text.Trim());
}
if (textBoxTelefono.Text.Trim() == "")
{
insertCommand.Parameters.AddWithValue("@telefono", DBNull.Value);
}
else
{
if (!int.TryParse(textBoxTelefono.Text.Trim(), out parsedValue))
{
lblErrorNumberValue.Visible = true;
lblErrorTelefono.Visible = true;
return;
}
insertCommand.Parameters.AddWithValue("@telefono", Decimal.Parse(textBoxTelefono.Text.Trim()));
}
insertCommand.Parameters.AddWithValue("@mail", textBoxMail.Text.Trim());
SqlParameter listRolesParam = insertCommand.Parameters.AddWithValue("@lista_Roles", rc);
listRolesParam.SqlDbType = SqlDbType.Structured;
listRolesParam.TypeName = "[CAIA_UNLIMITED].RolesLista";
SqlParameter listHotelesParam = insertCommand.Parameters.AddWithValue("@lista_Hoteles", hc);
listHotelesParam.SqlDbType = SqlDbType.Structured;
listHotelesParam.TypeName = "[CAIA_UNLIMITED].HotelesLista";
insertCommand.ExecuteNonQuery();
createConnection.Close();
string viewModificacion = string.Format("SELECT * FROM (SELECT u.usur_username as NombreDeUsuario, u.usur_habilitado as Habilitado, u.usur_nombre as Nombre, u.usur_apellido as Apellido, u.usur_documento_tipo as TipoDocumento, u.usur_documento Documento, u.usur_nacimiento as Nacimiento, u.usur_mail as Mail, d.dire_id as idDireccion, d.dire_pais as Pais, d.dire_telefono as Telefono, d.dire_dom_calle as Calle, d.dire_nro_calle as NumeroCalle, d.dire_piso Piso, d.dire_dpto as Departamento, d.dire_ciudad as Ciudad,ROW_NUMBER() OVER(PARTITION BY u.usur_username ORDER BY u.usur_username DESC) rn FROM CAIA_UNLIMITED.Usuario u JOIN CAIA_UNLIMITED.Usuario_X_Hotel uh on (u.usur_id = uh.usur_hote_idusur AND uh.usur_hote_idhote = '{0}') JOIN CAIA_UNLIMITED.Direccion d on (d.dire_id = u.dire_id OR d.dire_id IS NULL OR u.dire_id IS NULL)) a WHERE rn = 1", this.idHotel);
DataSet dsViewModificacion = DataBase.realizarConsulta(viewModificacion);
dataGridViewModificarUsuarios.DataSource = dsViewModificacion.Tables[0];
dataGridViewModificarUsuarios.AllowUserToAddRows = false;
string viewEliminar = string.Format("SELECT * FROM (SELECT u.usur_username as NombreDeUsuario, u.usur_habilitado as Habilitado, u.usur_nombre as Nombre, u.usur_apellido as Apellido, u.usur_documento_tipo as TipoDocumento, u.usur_documento Documento, u.usur_nacimiento as Nacimiento, u.usur_mail as Mail, d.dire_id as idDireccion, d.dire_pais as Pais, d.dire_telefono as Telefono, d.dire_dom_calle as Calle, d.dire_nro_calle as NumeroCalle, d.dire_piso Piso, d.dire_dpto as Departamento, d.dire_ciudad as Ciudad,ROW_NUMBER() OVER(PARTITION BY u.usur_username ORDER BY u.usur_username DESC) rn FROM CAIA_UNLIMITED.Usuario u JOIN CAIA_UNLIMITED.Usuario_X_Hotel uh on (u.usur_id = uh.usur_hote_idusur AND uh.usur_hote_idhote = '{0}') JOIN CAIA_UNLIMITED.Direccion d on (d.dire_id = u.dire_id OR d.dire_id IS NULL OR u.dire_id IS NULL) WHERE u.usur_habilitado = 1) a WHERE rn = 1", this.idHotel);
DataSet dsViewEliminar = DataBase.realizarConsulta(viewEliminar);
dataGridViewEliminarUsuarios.DataSource = dsViewEliminar.Tables[0];
dataGridViewEliminarUsuarios.AllowUserToAddRows = false;
MessageBox.Show("Usuario creado exitosamente!");
limpiarErrores();
limpiarListBox();
limpiarTextBox();
cargarComboBoxRol();
cargarComboBoxHotel();
}
catch (Exception errorDB)
{
MessageBox.Show(errorDB.Message);
}
}
}
}
public SqlParameter AddOutParm(string parameterName, SqlDbType dbType, int size)
{
SqlParameter outParm = SSqlParameter.CreateOut(parameterName, dbType, size);
return(AddSqlParm(parameterName, outParm));
}
private SqlParameter getSqlParameter(ParameterInfo parameterInfo, object value)
{
SqlParameter sp = new SqlParameter();
SqlCommandParameterAttribute sppa = parameterInfo.GetCustomAttribute <SqlCommandParameterAttribute>();
int size = 2048;
string name = null;
if (sppa != null)
{
name = sppa.Name;
size = sppa.Size;
}
name = String.IsNullOrEmpty(name) ? parameterInfo.Name : name;
sp.ParameterName = '@' + name;
if (value == null)
{
Type realType;
if (parameterInfo.ParameterType.IsByRef)
{
realType = parameterInfo.ParameterType.GetElementType();
}
else
{
realType = parameterInfo.ParameterType;
}
if (realType.Equals(typeof(string)))
{
sp.Value = DBNull.Value;
sp.SqlDbType = SqlDbType.NVarChar;
sp.Size = size;
}
else
{
if (realType.Equals(typeof(byte[])))
{
sp.Value = DBNull.Value;
sp.SqlDbType = SqlDbType.VarBinary;
sp.Size = size;
}
else
{
if (realType.IsGenericType && realType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
Type underType = realType.GetGenericArguments()[0];
sp.Value = Activator.CreateInstance(underType);
SqlDbType realDbType = sp.SqlDbType;
sp.Value = DBNull.Value;
sp.SqlDbType = realDbType;
}
}
}
}
else
{
sp.Value = value;
}
if (parameterInfo.IsRetval)
{
sp.Direction = ParameterDirection.ReturnValue;
}
else
{
if (parameterInfo.ParameterType.IsByRef)
{
sp.Direction = parameterInfo.IsOut ? ParameterDirection.Output : ParameterDirection.InputOutput;
}
}
return(sp);
}
public SqlParameter AddParm(string parameterName, object value)
{
SqlParameter inputParm = SSqlParameter.Create(parameterName, value);
return(AddSqlParm(parameterName, inputParm));
}
private bool CreatePretaskBySMS(Message message)
{
string returnValueParameterName = "@ReturnValueParameter";
try
{
using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
{
conn.Open();
using ( SqlCommand cmd = new SqlCommand("CreatePreTaskBySMS", conn) )
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Text", message.MessageBody);
cmd.Parameters.AddWithValue("@PhoneNumber", message.Number);
SqlParameter param = new SqlParameter();
param.ParameterName = returnValueParameterName;
param.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);
cmd.ExecuteScalar();
object returnValue = cmd.Parameters[returnValueParameterName].Value;
return true;
}
}
}
catch ( Exception exp )
{
NotifyOnError(exp);
}
return false;
}
internal virtual SqlExpression VisitParameter(SqlParameter p) {
return p;
}
public void DeleteVoteItemByVoteID(string strVoteID)
{
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@strVoteID", SqlDbType.NVarChar) };
pt[0].Value = strVoteID;
ShopMssqlHelper.ExecuteNonQuery(ShopMssqlHelper.TablePrefix + "DeleteVoteItemByVoteID", pt);
}
void autoLogin()
{
try
{
Data PGJ = new Data();
//PGJ.ConnectServer();
string unidad = string.Empty;
string user = string.Empty;
string pass = string.Empty;
if (Request.QueryString["unidad"] == "victoria")
{
unidad = "172.23.8.22";
user = "******";
pass = "******";
PGJ.ConnectServer(unidad);
}
else if (Request.QueryString["unidad"] == "tampico")
{
unidad = "10.8.167.20";
user = "******";
pass = "******";
PGJ.ConnectServer(unidad);
}
else if (Request.QueryString["unidad"] == "reynosa")
{
unidad = "10.8.42.14";
user = "******";
pass = "******";
PGJ.ConnectServer(unidad);
}
else if (Request.QueryString["unidad"] == "matamoros")
{
unidad = "10.8.32.21";
user = "******";
pass = "******";
PGJ.ConnectServer(unidad);
}
else if (Request.QueryString["unidad"] == "laredo")
{
unidad = "10.8.77.22";
user = "******";
pass = "******";
PGJ.ConnectServer(unidad);
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = Data.CnnCentral;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Cargar_Usuario_Login";
SqlParameter p1 = new SqlParameter("USUARIO", user); //txtUsuario.Text);
SqlParameter p2 = new SqlParameter("PASSWORD", pass); //txtPass.Text);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
SqlDataReader rd = cmd.ExecuteReader();
//SqlCommand cmd = new SqlCommand("sp_Cargar_Usuario_Login " + txtUsuario.Text + "," + txtPass.Text, Data.CnnCentral);
//SqlDataReader rd = cmd.ExecuteReader();
//Data PGJ = new Data();
//PGJ.ConnectServer();
//SqlCommand cmd = new SqlCommand("sp_Cargar_Usuario_Login " + txtUsuario.Text + "," + txtPass.Text, Data.CnnCentral);
//SqlDataReader rd = cmd.ExecuteReader();
if (rd.HasRows)
{
rd.Read();
Session["IdUsuarioRemitir"] = 0;
Session["SIGLA"] = rd["SIGLA"].ToString();
Session["IdUsuario"] = rd["id_usuario"].ToString();
Session["IdUnidad"] = rd["Id_Undd"].ToString();
Session["IdMunicipio"] = rd["ID_MNCPIO"].ToString();
Data.IdMunicipio = short.Parse(rd["ID_MNCPIO"].ToString());
Session["IdModulo"] = rd["ID_MODULO"].ToString();
Session["UNDD_DSCRPCION"] = rd["UNDD_DSCRPCION"].ToString();
Session["PUESTO"] = rd["PUESTO"].ToString();
Session["ID_PUESTO"] = rd["ID_PUESTO"].ToString();
Session["USUARIO"] = rd["USUARIO"].ToString();
Session["IP_MAQUINA"] = IP_MAQUINA.Text.ToString();
Session["IdModuloBitacora"] = "4";
Session["CNTRSNA_DEFAULT"] = rd["CNTRSNA_DEFAULT"].ToString();
Session.Timeout = 300;
Session["Us"] = rd["NMBRE"].ToString() + " " + rd["PTRNO"].ToString() + " " + rd["MTRNO"].ToString();
if (Session["IdUsuario"].ToString() == "4")
{
Response.Redirect("Plantillas.aspx");
}
rd.Dispose();
if (Session["CNTRSNA_DEFAULT"].ToString() == "True")
{
//PGJ.InsertarBitacora(int.Parse(Session["IdUsuario"].ToString()),"", IP_MAQUINA.Text,"", 1, "REALIZANDO SU CAMBIO DE CONTRASEÑA");
PGJ.InsertarBitacora(int.Parse(Session["IdUsuario"].ToString()), Session["IP_MAQUINA"].ToString(), HttpContext.Current.Request.Url.AbsoluteUri, 2, "Cambio de Contraseña", 0);
Response.Redirect("SP_Cambio_Contra.aspx");
}
if (Session["IdModulo"].ToString() == "1")
{
Response.Redirect("PruebaPantallas.aspx");
}
else if (Session["IdModulo"].ToString() == "2")
{
Response.Redirect("ConsultaRAC.aspx");
}
else if (Session["IdModulo"].ToString() == "3")
{
Response.Redirect("Mediacion.aspx");
}
else if (Session["IdModulo"].ToString() == "4")
{
Response.Redirect("AgenciaInvestigacion.aspx");
}
else if (Session["IdModulo"].ToString() == "5")
{
Response.Redirect("ConsultaAtencionComunidad.aspx");
}
else if (Session["IdModulo"].ToString() == "6")
{
Response.Redirect("Administrar.aspx");
}
else if (Session["IdModulo"].ToString() == "7")
{
Response.Redirect("WebEstadistica.aspx");
}
else if (Session["IdModulo"].ToString() == "8")
{
if (Session["ID_PUESTO"].ToString() == "8")
{
Response.Redirect("PoliciaInvestigador.aspx");
}
else if (Session["ID_PUESTO"].ToString() == "16")
{
Response.Redirect("CoordPoliciaInvestigador.aspx");
}
}
else if (Session["IdModulo"].ToString() == "9")
{
txtUsuario.Text = Session["IdModulo"].ToString();
if (Session["ID_PUESTO"].ToString() == "22")
{
txtUsuario.Text = Session["ID_PUESTO"].ToString();
Response.Redirect("Periciales.aspx");
}
else if (Session["ID_PUESTO"].ToString() == "21")
{
txtUsuario.Text = Session["ID_PUESTO"].ToString();
Response.Redirect("DireccionServiciosPericiales.aspx");
}
}
else if (Session["IdModulo"].ToString() == "10")
{
Response.Redirect("AcuerdosReparatorios.aspx");
}
else if (Session["IdModulo"].ToString() == "11")
{
Response.Redirect("UnidadSecuerstros.aspx");
}
else if (Session["IdModulo"].ToString() == "12")
{
PGJ.InsertarBitacora(int.Parse(Session["IdUsuario"].ToString()), Session["IP_MAQUINA"].ToString(), HttpContext.Current.Request.Url.AbsoluteUri, 1, "Inicio de Sesion", int.Parse(Session["IdModuloBitacora"].ToString()));
Response.Redirect("UnidadPNL.aspx");
//Response.Redirect("PNLUnidades.aspx");
}
}
else
{
lblError.Text = "USUARIO O CONTRASEÑA INVÁLIDO";
}
//rd.Close();
rd.Dispose();
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("server=SEBASTIAN ; database=RegistroWebService ; integrated security = true");
conexion.Open();
MessageBox.Show("Se abrió la conexión con el servidor SQL Server y se seleccionó la base de datos");
//conexion.Close();
//MessageBox.Show("Se cerró la conexión.");
SqlCommand myCommand = new SqlCommand("set_insertarRegistro ", conexion);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameterNombre = new SqlParameter("@Nombre", SqlDbType.VarChar, 50);
parameterNombre.Value = nombre.Text;
myCommand.Parameters.Add(parameterNombre);
SqlParameter parameterApellido = new SqlParameter("@Apellido", SqlDbType.VarChar, 50);
parameterApellido.Value = apellido.Text;
myCommand.Parameters.Add(parameterApellido);
SqlParameter parameterTelefono = new SqlParameter("@Telefono", SqlDbType.VarChar, 50);
parameterTelefono.Value = telefono.Text;
myCommand.Parameters.Add(parameterTelefono);
SqlParameter parameterPais = new SqlParameter("@Pais", SqlDbType.VarChar, 50);
parameterPais.Value = textBox1.Text;
myCommand.Parameters.Add(parameterPais);
SqlParameter parameterCodPais = new SqlParameter("@CodPais", SqlDbType.VarChar, 50);
parameterCodPais.Value = textBox2.Text;
myCommand.Parameters.Add(parameterCodPais);
SqlParameter parameterMoneda = new SqlParameter("@Moneda", SqlDbType.VarChar, 50);
parameterMoneda.Value = textBox3.Text;
myCommand.Parameters.Add(parameterMoneda);
SqlParameter parameterCodMoneda = new SqlParameter("@CodMoneda", SqlDbType.VarChar, 50);
parameterCodMoneda.Value = textBox4.Text;
myCommand.Parameters.Add(parameterCodMoneda);
SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.VarChar, 50);
parameterEmail.Value = textBox5.Text;
myCommand.Parameters.Add(parameterEmail);
SqlParameter parameterTipoTarjeta = new SqlParameter("@TipoTarjeta", SqlDbType.VarChar, 50);
parameterTipoTarjeta.Value = textBox6.Text;
myCommand.Parameters.Add(parameterTipoTarjeta);
SqlParameter parameterNumTarjeta = new SqlParameter("@NumTarjeta", SqlDbType.VarChar, 50);
parameterNumTarjeta.Value = textBox7.Text;
myCommand.Parameters.Add(parameterNumTarjeta);
SqlParameter parameterPassword = new SqlParameter("@Password", SqlDbType.VarChar, 50);
parameterPassword.Value = password.Text;
myCommand.Parameters.Add(parameterPassword);
SqlDataReader datDatos_t = myCommand.ExecuteReader();
//if (datDatos_t.Read())
//{
// this.strCodigo_l = strCodigo_p;
// this.strPais_l = datDatos_t.GetString(datDatos_t.GetOrdinal("CODPAI"));
// this.strNombre_l = datDatos_t.GetString(datDatos_t.GetOrdinal("NOMCIU"));
// this.intDepartamento_l = Int32.Parse((datDatos_t.GetValue(datDatos_t.GetOrdinal("CODDEP")).ToString()));
// this.strEstado_l = datDatos_t.GetString(datDatos_t.GetOrdinal("ESTADO"));
//}
datDatos_t.Close();
conexion.Close();
}
internal override SqlExpression VisitParameter(SqlParameter p) {
SqlParameter n = new SqlParameter(p.ClrType, p.SqlType, p.Name, p.SourceExpression);
n.Direction = p.Direction;
return n;
}
public static EntityMaintResult AddNewEntity(int LkEntityType, int LKEntityType2, string FYend, string Website, string Email, string HomePhone, string WorkPhone, string CellPhone, string Stvendid,
string ApplicantName, string Fname, string Lname, int Position, string Title, string FarmName, int LkFVEnterpriseType, int AcresInProduction,
int AcresOwned, int AcresLeased, int AcresLeasedOut, int TotalAcres, bool OutOFBiz, string Notes, string AgEd, int YearsManagingFarm, int? AppRole,
int Operation)
{
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "AddNewEntity";
command.Parameters.Add(new SqlParameter("LkEntityType", LkEntityType));
command.Parameters.Add(new SqlParameter("LKEntityType2", LKEntityType2));
command.Parameters.Add(new SqlParameter("FYend", FYend));
command.Parameters.Add(new SqlParameter("Website", Website));
command.Parameters.Add(new SqlParameter("Email", Email));
command.Parameters.Add(new SqlParameter("HomePhone", HomePhone));
command.Parameters.Add(new SqlParameter("CellPhone", CellPhone));
command.Parameters.Add(new SqlParameter("WorkPhone", WorkPhone));
command.Parameters.Add(new SqlParameter("Stvendid", Stvendid));
command.Parameters.Add(new SqlParameter("ApplicantName", ApplicantName));
command.Parameters.Add(new SqlParameter("Fname", Fname));
command.Parameters.Add(new SqlParameter("Lname", Lname));
command.Parameters.Add(new SqlParameter("Position", Position));
command.Parameters.Add(new SqlParameter("Title", Title == "" ? System.Data.SqlTypes.SqlString.Null : Title));
command.Parameters.Add(new SqlParameter("FarmName", FarmName));
command.Parameters.Add(new SqlParameter("LkFVEnterpriseType", LkFVEnterpriseType));
command.Parameters.Add(new SqlParameter("AcresInProduction", AcresInProduction));
command.Parameters.Add(new SqlParameter("AcresOwned", AcresOwned));
command.Parameters.Add(new SqlParameter("AcresLeased", AcresLeased));
command.Parameters.Add(new SqlParameter("AcresLeasedOut", AcresLeasedOut));
command.Parameters.Add(new SqlParameter("TotalAcres", TotalAcres));
command.Parameters.Add(new SqlParameter("OutOFBiz", OutOFBiz));
command.Parameters.Add(new SqlParameter("Notes", Notes));
command.Parameters.Add(new SqlParameter("AgEd", AgEd));
command.Parameters.Add(new SqlParameter("YearsManagingFarm", YearsManagingFarm));
command.Parameters.Add(new SqlParameter("AppRole", AppRole));
command.Parameters.Add(new SqlParameter("Operation", Operation));
SqlParameter parmMessage = new SqlParameter("@isDuplicate", SqlDbType.Bit);
parmMessage.Direction = ParameterDirection.Output;
command.Parameters.Add(parmMessage);
SqlParameter parmMessage2 = new SqlParameter("@DuplicateId", SqlDbType.Int);
parmMessage2.Direction = ParameterDirection.Output;
command.Parameters.Add(parmMessage2);
SqlParameter parmMessage1 = new SqlParameter("@ApplicantId", SqlDbType.Int);
parmMessage1.Direction = ParameterDirection.Output;
command.Parameters.Add(parmMessage1);
command.CommandTimeout = 60 * 5;
command.ExecuteNonQuery();
EntityMaintResult ap = new EntityMaintResult();
ap.IsDuplicate = DataUtils.GetBool(command.Parameters["@isDuplicate"].Value.ToString());
ap.DuplicateId = DataUtils.GetInt(command.Parameters["@DuplicateId"].Value.ToString());
ap.ApplicantId = DataUtils.GetInt(command.Parameters["@ApplicantId"].Value.ToString());
return ap;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override bool BuildAsValue(SqlParameter parm)
{
if (parm.Value is DateTime)
{
var dt = (DateTime) parm.Value;
return dt.Millisecond == 0 && !parm.IsQueryParameter;
}
return base.BuildAsValue(parm);
}
public void AddParam(SqlParameter param)
{
_SqlParams.Add(param);
}
/// <summary>
/// Visits the specified <see cref="SqlParameter"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlParameter expression)
{
}
/// <summary>
/// Добавляет новый параметр в лист SqlParameter
/// </summary>
/// <param name="sqlParameter">Готовый параметр</param>
public void AddParameter(SqlParameter sqlParameter)
{
_lstParameters.Add(sqlParameter);
}
/// <summary>
/// resolve at run time the appropriate set of SqlParameters for a stored procedure
/// 在运行时得到一个存储过程的一系列参数信息
/// </summary>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="connectionString">一个连接对象的有效连接串</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="spName">存储过程名</param>
/// <param name="includeReturnValueParameter">是否有返回值参数</param>
/// <returns>参数对象数组，存储过程的所有参数信息</returns>
private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(spName, cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;
//从 SqlCommand 指定的存储过程中检索参数信息，并填充指定的 SqlCommand 对象的 Parameters 集。
SqlCommandBuilder.DeriveParameters(cmd);
if (!includeReturnValueParameter)
{
//移除第一个参数对象，因为没有返回值，而默认情况下，第一个参数对象是返回值
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; ;
cmd.Parameters.CopyTo(discoveredParameters, 0);
return discoveredParameters;
}
}
public List<TimeTable> Db_Get_TimeTable()
{
string query = "SELECT * FROM [TimeTable]";
SqlParameter[] sqlParameters = new SqlParameter[0];
return ReadTables(ExecuteSelectQuery(query, sqlParameters));
}
private object executeCommand(MethodInfo methodInfo, SqlCommand command, ref bool mustClosed, SpmSharedItemPool itemPool)
{
object result = null;
Type retType = methodInfo.ReturnType;
if (typeof(void).Equals(retType))
{
// no result, execute non-query
command.ExecuteNonQuery();
}
else
{
if (typeof(SqlReturn).Equals(retType))
{
// no result, execute non-query with return value
SqlParameter retVal = command.Parameters.Add(
new SqlParameter("@RETURN_VALUE", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
});
command.ExecuteNonQuery();
result = new SqlReturn(retVal.Value is int?(int)retVal.Value: 0);
}
else
{
if (typeof(SqlDataReader).Equals(retType))
{
result = command.ExecuteReader(CommandBehavior.CloseConnection);
mustClosed = false;
}
else
{
if (typeof(DataTable).Equals(retType))
{
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
result = ds.Tables[0];
ds.Tables.RemoveAt(0);
}
}
}
else
{
if (typeof(DataSet).Equals(retType))
{
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();
da.Fill(ds);
result = ds;
}
}
else
{
if (retType.IsGenericType &&
retType.GetGenericTypeDefinition().Equals(typeof(IEnumerable <>)))
{
ReadEnumDelegate readEnum = GetReadEnum(retType.GetGenericArguments()[0]);
result = readEnum(command.ExecuteReader(CommandBehavior.CloseConnection), itemPool);
mustClosed = false;
}
else
{
result = command.ExecuteScalar();
//throw new ArgumentException(String.Concat("Unsupported return type: ",
// methodInfo.ReturnType.ToString()));
}
}
}
}
}
}
return(result);
}
protected override SqlExpression VisitParameter(SqlParameter expr)
{
_builder.Append(expr.Name);
return expr;
}
public Int64 saveInward(IEnumerable <InwardDetail> inward)
{
DataTable table = new DataTable();
table.Columns.Add("InwardDetailId", typeof(Int64));
table.Columns.Add("ProductId", typeof(int));
table.Columns.Add("Quantity", typeof(int));
table.Columns.Add("BillDate", typeof(DateTime));
foreach (var inw in inward)
{
table.Rows.Add(
inw.InwardDetailId,
inw.ProductId,
inw.Quantity,
inw.Dated
);
}
Database db;
DatabaseProviderFactory factory = new DatabaseProviderFactory();
db = factory.Create("OPSDBConn");
//IDataReader reader = db.ExecuteReader(CommandType.Text, "select * from ProductMaster");
//SqlParameter parameter = new SqlParameter();
////The parameter for the SP must be of SqlDbType.Structured
//parameter.ParameterName = "@tvp";
////parameter.SqlDbType = System.Data.SqlDbType.Structured;
//parameter.TypeName = "dbo.OrderDetailTableType";
//parameter.Value = table;
//parameter.SqlDbType = SqlDbType.Structured;
//DbCommand dbCommand = db.GetStoredProcCommand("sp_OrderDetail");
Int64 outputParam;
using (SqlConnection sqlConnection = (SqlConnection)db.CreateConnection())
{
// Define a command object for calling the stored procedure.
// Note: The Parameters collection of the SqlCommand object automatically assigns the
// DbType property of each parameter to Object, and the SqlDbType property to Structured.
//
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_InwardDetail";
sqlCommand.Parameters.AddWithValue("@tvp", table);
SqlParameter OutputParam = new SqlParameter("@O_InwardNo", SqlDbType.BigInt);
OutputParam.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(OutputParam);
// Open the connection, execute the command and close the connection.
//
if (sqlCommand.Connection.State != ConnectionState.Open)
{
sqlCommand.Connection.Open();
}
sqlCommand.ExecuteNonQuery();
var str = sqlCommand.Parameters["@O_InwardNo"].Value;
outputParam = Convert.ToInt64(str);
if (sqlCommand.Connection.State == ConnectionState.Open)
{
sqlCommand.Connection.Close();
}
}
return(outputParam);
}
public void AddParameter(string paramname, object paramvalue)
{
SqlParameter param = new SqlParameter(paramname, paramvalue);
cmd.Parameters.Add(param);
}
internal override SqlExpression VisitClientParameter(SqlClientParameter cp) {
if (cp.SqlType.CanBeParameter) {
SqlParameter p = new SqlParameter(cp.ClrType, cp.SqlType, this.parameterizer.CreateParameterName(), cp.SourceExpression);
this.currentParams.Add(new SqlParameterInfo(p, cp.Accessor.Compile()));
return p;
}
else {
return cp;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlWhere"/> class.
/// </summary>
/// <param name="whereClause">The where clause.</param>
/// <param name="parameters">The parameters.</param>
public SqlWhere(string whereClause, SqlParameter[] parameters)
{
WhereClause = whereClause;
Parmeters = parameters;
}
protected void submit(object sender, EventArgs e)
{
int min_exp = 0;
int salary = 0;
int vaccancies = 0;
int workingh = 0;
int Qnumber = 0;
string connStr = ConfigurationManager.ConnectionStrings["iwork"].ToString();
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("HR_Add_Job", conn);
cmd.CommandType = CommandType.StoredProcedure;
string userId = Session["Username"].ToString();
string dep_role = txt_dep_role.Text;
string department = txt_department.Text;
string title = txt_title.Text;
string company = txt_company.Text;
string short_discrip = txt_short_discrip.Text;
string long_dscrip = txt_long_discrip.Text;
Int32.TryParse(txt_min_exp.Text, out min_exp);
Int32.TryParse(txt_salary.Text, out salary);
string deadline = txt_deadline.Text;
Int32.TryParse(txt_vaccancies.Text, out vaccancies);
Int32.TryParse(txt_workingh.Text, out workingh);
Int32.TryParse(txt_workingh.Text, out Qnumber);
string question = txt_question.Text;
bool answer = Ddanswer.SelectedItem.Equals("1");
cmd.Parameters.Add(new SqlParameter("@HR_username", userId));
cmd.Parameters.Add(new SqlParameter("@department", department));
cmd.Parameters.Add(new SqlParameter("@dep_role", dep_role));
cmd.Parameters.Add(new SqlParameter("@title", title));
cmd.Parameters.Add(new SqlParameter("@company", company));
cmd.Parameters.Add(new SqlParameter("@short_discription", short_discrip));
cmd.Parameters.Add(new SqlParameter("@detailed_discription", long_dscrip));
cmd.Parameters.Add(new SqlParameter("@min_experience", min_exp));
cmd.Parameters.Add(new SqlParameter("@salary", salary));
cmd.Parameters.Add(new SqlParameter("@deadline", deadline));
cmd.Parameters.Add(new SqlParameter("@working_hours", workingh));
cmd.Parameters.Add(new SqlParameter("@Qnumber ", Qnumber));
cmd.Parameters.Add(new SqlParameter("@no_of_vacancies", vaccancies));
cmd.Parameters.Add(new SqlParameter("@question ", question));
cmd.Parameters.Add(new SqlParameter("@answer", answer));
SqlParameter count = cmd.Parameters.Add("@count", SqlDbType.Int);
count.Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
if (count.Value.ToString().Equals("1"))
{
Response.Write("Job added");
}
else
{
if (count.Value.ToString().Equals("2"))
{
Response.Write("JOB ALREADY EXSISTS.");
}
}
}
private SqlExpression ExtractParameter(SqlExpression expr) {
Type clrType = expr.ClrType;
if (expr.ClrType.IsValueType && !TypeSystem.IsNullableType(expr.ClrType)) {
clrType = typeof(Nullable<>).MakeGenericType(expr.ClrType);
}
this.externals.Add(expr);
SqlParameter sp = new SqlParameter(clrType, expr.SqlType, "@x" + (this.parameters.Count + 1), expr.SourceExpression);
this.parameters.Add(sp);
return sp;
}
private void InitializateHeaderData()
{
if (string.IsNullOrEmpty(TableName))
{
return;
}
string tableName = TableName.Replace("detail", "");
string sSelect = "SELECT Clinic.Name AS ClinicName, [" + tableName + "].ClientID";
string sInnerjoin = "\n INNER JOIN Clinic ON [" + tableName + "].ClientID = Clinic.Id";
string sWhere = "\n WHERE 1=1 ";
string sSQL = string.Empty;
string sListFields = string.Empty;
List <SqlParameter> parames = new List <SqlParameter>();
int i = -1;
if (HeaderConditions != null && !string.IsNullOrEmpty(tableName) && tableName.Length > 0)
{
var conditionTables = new Hashtable();
conditionTables.Add("Clinic", "Clinic");
foreach (SearchExpander seardcondition in HeaderConditions)
{
i++;
SqlParameter param = null;
if (seardcondition.Type == null)
{
sSelect += ", [" + tableName + "]." + seardcondition.ColumnName;
}
if (seardcondition.Refference != null)
{
string BeetwenRefTableName = WebCore.GetTableName(seardcondition.Type);
string RefTableName = WebCore.GetTableName(seardcondition.Refference);
//Add column to sellect
if (seardcondition.Type == null)
{
sSelect += ", [" + RefTableName + "]." + seardcondition.DisplayRefferenceColumn + " as " + RefTableName + seardcondition.DisplayRefferenceColumn;
}
else
{
sSelect += ", [" + RefTableName + "]." + seardcondition.DisplayRefferenceColumn + " as " + RefTableName + seardcondition.DisplayRefferenceColumn;
}
//===========================================================================
//Join table has column refference
if (!conditionTables.ContainsKey(RefTableName))
{
if (seardcondition.Type == null)
{
sInnerjoin += "\n LEFT OUTER JOIN [" + RefTableName + "] ON [" + RefTableName + "]." + seardcondition.RefferenceColumn + " = [" + tableName + "]." + seardcondition.ColumnName;
conditionTables.Add(RefTableName, RefTableName);
}
else
{
sInnerjoin += "\n LEFT OUTER JOIN [" + RefTableName + "] ON [" + RefTableName + "]." + seardcondition.RefferenceColumn + " = [" + BeetwenRefTableName + "]." + seardcondition.ColumnName;
conditionTables.Add(RefTableName, RefTableName);
}
}
}
//Check condition
else
{
}
}
// Check with client ID
if (!string.IsNullOrEmpty(ClientID))
{
sWhere += " AND [" + tableName + "].ClientId=" + ClientId;
}
if (!string.IsNullOrEmpty(Id))
{
sWhere += " AND [" + tableName + "].Id=" + Id;
}
if (tableName == "medicineplandetail")
{
sWhere = sWhere.Replace("medicineplanId", "PlanId");
}
// Group all querry
sSelect += " FROM [" + tableName + "]";
sSQL = sSelect + sInnerjoin + sWhere;
DataSet dataset = SqlHelper.ExecuteDataset(Config.SVConnectionString, CommandType.Text, sSQL, null);
if (this.HeaderConditions != null)
{
foreach (SearchExpander searchCondition in this.HeaderConditions)
{
string RefTableName = WebCore.GetTableName(searchCondition.Refference);
if (searchCondition.Refference != null)
{
searchCondition.Value = string.IsNullOrEmpty(searchCondition.DisplayFormat) ? dataset.Tables[0].Rows[0][RefTableName + searchCondition.DisplayRefferenceColumn] : string.Format(searchCondition.DisplayFormat, dataset.Tables[0].Rows[0][RefTableName + searchCondition.DisplayRefferenceColumn]);
}
else
{
searchCondition.Value = string.IsNullOrEmpty(searchCondition.DisplayFormat) ? dataset.Tables[0].Rows[0][searchCondition.ColumnName] : string.Format(searchCondition.DisplayFormat, dataset.Tables[0].Rows[0][searchCondition.ColumnName]);
}
if (string.IsNullOrEmpty(searchCondition.DisplayFormat) && dataset.Tables[0].Rows[0][searchCondition.ColumnName].GetType() == typeof(DateTime))
{
searchCondition.Value = ((DateTime)dataset.Tables[0].Rows[0][searchCondition.ColumnName]).ToString("dd/MM/yyyy");
}
}
}
this.DataBind();
if (this.HeaderConditions != null && this.HeaderConditions.Count > 0)
{
List <SearchExpander> searchcdt = new List <SearchExpander>();
foreach (SearchExpander c in headerConditions)
{
if (c.BeenSearch)
{
searchcdt.Add(c);
}
}
rptConditions.DataSource = searchcdt;
rptConditions.DataBind();
}
}
}
//复制缓存SqlParameter参数数组（深克隆）
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
private void LoadList()
{
if (string.IsNullOrEmpty(TableName))
{
return;
}
string sSelect = "SELECT Clinic.Name AS ClinicName, [" + TableName + "].ClientID";
string sInnerjoin = "\n INNER JOIN Clinic ON [" + TableName + "].ClientID = Clinic.Id";
string sWhere = "\n WHERE 1=1 ";
string sSQL = string.Empty;
string sListFields = string.Empty;
List <SqlParameter> parames = new List <SqlParameter>();
int i = -1;
if (DetailConditions != null && !string.IsNullOrEmpty(TableName) && TableName.Length > 0)
{
var conditionTables = new Hashtable();
conditionTables.Add("Clinic", "Clinic");
foreach (SearchExpander seardcondition in DetailConditions)
{
i++;
SqlParameter param = null;
if (seardcondition.Type == null)
{
sSelect += ", [" + TableName + "]." + seardcondition.ColumnName;
}
if (seardcondition.Refference != null)
{
string RefTableName = WebCore.GetTableName(seardcondition.Refference);
string BeetwenRefTableName = WebCore.GetTableName(seardcondition.Type);
//Add column to sellect
if (seardcondition.Type == null)
{
sSelect += ", [" + RefTableName + "]." + seardcondition.DisplayRefferenceColumn + " as " + RefTableName + seardcondition.DisplayRefferenceColumn;
}
else
{
sSelect += ", [" + RefTableName + "]." + seardcondition.DisplayRefferenceColumn + " as " + RefTableName + seardcondition.DisplayRefferenceColumn;
}
//===========================================================================
//Join table has column refference
if (!conditionTables.ContainsKey(RefTableName))
{
if (seardcondition.Type == null)
{
sInnerjoin += "\n LEFT OUTER JOIN [" + RefTableName + "] ON [" + RefTableName + "]." + seardcondition.RefferenceColumn + " = [" + TableName + "]." + seardcondition.ColumnName;
conditionTables.Add(RefTableName, RefTableName);
}
else
{
sInnerjoin += "\n LEFT OUTER JOIN [" + RefTableName + "] ON [" + RefTableName + "]." + seardcondition.RefferenceColumn + " = [" + BeetwenRefTableName + "]." + seardcondition.ColumnName;
conditionTables.Add(RefTableName, RefTableName);
}
}
}
//Check condition
else
{
}
}
// Check with client ID
if (!string.IsNullOrEmpty(ClientID))
{
sWhere += " AND [" + TableName + "].ClientId=" + ClientId;
}
if (!string.IsNullOrEmpty(Id))
{
sWhere += " AND [" + TableName + "]." + TableName.Replace("detail", "") + "Id=" + Id;
}
if (TableName == "medicineplandetail")
{
sWhere = sWhere.Replace("medicineplanId", "PlanId");
}
// Group all querry
sSelect += " FROM [" + TableName + "]";
sSQL = sSelect + sInnerjoin + sWhere;
DataSet dataset = SqlHelper.ExecuteDataset(Config.SVConnectionString, CommandType.Text, sSQL, parames.ToArray());
gvListData.AutoGenerateColumns = false;
gvListData.DataSource = dataset;
gvListData.DataBind();
if (dataset != null && dataset.Tables.Count > 0)
{
pager.ItemCount = dataset.Tables[0].Rows.Count;
}
}
}
protected override void BuildLikePredicate(SelectQuery.Predicate.Like predicate)
{
if (predicate.Escape != null)
{
if (predicate.Expr2 is SqlValue && predicate.Escape is SqlValue)
{
var text = ((SqlValue)predicate.Expr2).Value.ToString();
var val = new SqlValue(ReescapeLikeText(text, (char)((SqlValue)predicate.Escape).Value));
predicate = new SelectQuery.Predicate.Like(predicate.Expr1, predicate.IsNot, val, null);
}
else if (predicate.Expr2 is SqlParameter)
{
var p = (SqlParameter)predicate.Expr2;
if (p.LikeStart != null)
{
var value = (string)p.Value;
if (value != null)
{
value = value.Replace("[", "[[]").Replace("~%", "[%]").Replace("~_", "[_]").Replace("~~", "[~]");
p = new SqlParameter(p.SystemType, p.Name, value) { DbSize = p.DbSize, DataType = p.DataType, IsQueryParameter = p.IsQueryParameter };
predicate = new SelectQuery.Predicate.Like(predicate.Expr1, predicate.IsNot, p, null);
}
}
}
}
base.BuildLikePredicate(predicate);
}
//metodo insertar
public string Insertar(DIngreso Ingreso,List<DDetalle_Ingreso>Detalle)
{
string rpta = "";
SqlConnection SqlCon = new SqlConnection();
try
{
SqlCon.ConnectionString = Conexion.Cn;
SqlCon.Open();
SqlTransaction SqlTra = SqlCon.BeginTransaction();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlCon;
SqlCmd.Transaction = SqlTra;
SqlCmd.CommandText = "spinsertar_ingreso";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter ParIdingreso = new SqlParameter();
ParIdingreso.ParameterName = "@idingreso";
ParIdingreso.SqlDbType = SqlDbType.Int;
ParIdingreso.Direction = ParameterDirection.Output;
SqlCmd.Parameters.Add(ParIdingreso);
SqlParameter ParIdTrabajador = new SqlParameter();
ParIdTrabajador.ParameterName = "@idtrabajador";
ParIdTrabajador.SqlDbType = SqlDbType.Int;
ParIdTrabajador.Value = Ingreso.Idtrabajador;
SqlCmd.Parameters.Add(ParIdTrabajador);
SqlParameter ParIdproveedor = new SqlParameter();
ParIdproveedor.ParameterName = "@idproveedor";
ParIdproveedor.SqlDbType = SqlDbType.Int;
ParIdproveedor.Value = Ingreso.Idproveedor;
SqlCmd.Parameters.Add(ParIdproveedor);
SqlParameter ParFecha = new SqlParameter();
ParFecha.ParameterName = "@fecha";
ParFecha.SqlDbType = SqlDbType.VarChar;
ParFecha.Value = Ingreso.Fecha;
SqlCmd.Parameters.Add(ParFecha);
SqlParameter ParTipo_Comprobante = new SqlParameter();
ParTipo_Comprobante.ParameterName = "@tipo_comprobante";
ParTipo_Comprobante.SqlDbType = SqlDbType.VarChar;
ParTipo_Comprobante.Size = 20;
ParTipo_Comprobante.Value = Ingreso.Tipo_Comprobante;
SqlCmd.Parameters.Add(ParTipo_Comprobante);
rpta = SqlCmd.ExecuteNonQuery() == 1 ? "OK" : "NO se Ingreso el Registro";
if (rpta.Equals("OK"))
{
this.Idingreso = Convert.ToInt32(SqlCmd.Parameters["@idingreso"].Value);
foreach (DDetalle_Ingreso det in Detalle)
{
det.Idingreso = this.Idingreso;
rpta = det.Insertar(det, ref SqlCon, ref SqlTra);
if (!rpta.Equals("OK"))
{
break;
}
}
}
if (rpta.Equals("OK"))
{
SqlTra.Commit();
}
else
{
SqlTra.Rollback();
}
}
catch (Exception ex)
{
rpta = ex.Message;
}
finally
{
if (SqlCon.State == ConnectionState.Open) SqlCon.Close();
}
return rpta;
}