public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs)
{
SqlConnection sqlConn = new SqlConnection(); //defines database connection
SqlCommand sqlCmd = new SqlCommand(); //defines what to do
SqlDataAdapter sqlAdap = new SqlDataAdapter();
try
{
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
sqlConn.Open();
sqlCmd.CommandText = "SP_Tender_FindRecord";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = sqlConn;
SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar);
parm1.Value = ProcessId;
parm1.Direction = ParameterDirection.Input;
sqlCmd.Parameters.Add(parm1);
sqlAdap.SelectCommand = sqlCmd;
sqlAdap.Fill(ReturnDs);
return string.Empty;
}
catch (Exception err)
{
return err.Message;
}
finally
{ sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
}
public InvoiceObject getInvoiceInfo(int invoice_Number, string connectionString, UserObject user)
{
DataTable results = new DataTable();
try
{
conn = DBUtility.createConnection(connectionString);
SqlCommand cmdIns = DBUtility.PrepareReadCommand("GetAnInvoice", conn);
cmdIns.Parameters.Add( new SqlParameter("@Invoice_Number", invoice_Number));
using (conn)
using (cmdIns)
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdIns))
{
dataAdapter.Fill(results);
}
var invoice = populateInvoice(results.Rows[0], connectionString, user);
cmdIns.Dispose();
DBUtility.Close(conn);
conn.Dispose();
return invoice;
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
DBUtility.Close(conn);
conn.Dispose();
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string sConnectionString = "data source=cypress.csil.sfu.ca;" + "initial catalog=hga13354;" + "Trusted_Connection=yes;";
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlCommand cmd = new SqlCommand("select s_ID, s_pw from dbo.Student where s_ID= '" + textBox1.Text + "' and s_pw= '" + textBox2.Text + "' ", objConn);
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
if (sdr.HasRows)
{
Form_search f2 = new Form_search();
f2.s_IS = textBox1.Text.ToString();
this.Hide();
f2.Show();
objConn.Close();
objConn.Dispose();
}
else
{
MessageBox.Show("Incorrect Student ID or Password");
objConn.Close();
objConn.Dispose();
}
objConn.Close();
objConn.Dispose();
}
catch (SqlException sqlEx) { MessageBox.Show("SQL Server Error Message:" + sqlEx.Message); }
}
private void borraFalta(int fila, int columna)
{
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
fh = new Faltas.Core.FaltasHelper();
fh.Command = cmd;
Faltas.Core.Faltas falta = new Faltas.Core.Faltas();
falta.idtrabajador = int.Parse(dgvFaltas.Rows[fila].Cells["idtrabajadorfalta"].Value.ToString());
falta.fechainicio = periodoInicio.Date;
falta.fechafin = periodoFin.Date;
falta.fecha = DateTime.Parse(dgvFaltas.Columns[columna].Name.ToString());
try
{
cnx.Open();
fh.eliminaFaltaExistente(falta);
cnx.Close();
cnx.Dispose();
}
catch (Exception error)
{
MessageBox.Show("Error: \r\n \r\n" + error.Message, "Error");
cnx.Dispose();
}
//if (dgvFaltas.Columns[columna].Name == "falta"){}
}
private void btnAceptar_Click(object sender, EventArgs e)
{
int entero = 0;
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
ch = new Configuracion.Core.ConfiguracionHelper();
ch.Command = cmd;
try
{
cnx.Open();
ch.actualizarValorConfiguracion(1, txtCorreoEmisor.Text);
ch.actualizarValorConfiguracion(2, txtPassword.Text);
ch.actualizarValorConfiguracion(3, txtPuerto.Text);
entero = Convert.ToInt32(chkSsl.Checked);
ch.actualizarValorConfiguracion(4, entero.ToString());
ch.actualizarValorConfiguracion(5, txtServidorEnvio.Text);
ch.actualizarValorConfiguracion(6, txtRuta.Text);
cnx.Close();
cnx.Dispose();
this.Dispose();
}
catch (Exception error)
{
MessageBox.Show("Error: Al obtener alguna de las configuraciones.\r\n\r\n" + error.Message, "Error");
cnx.Dispose();
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string SQLString,params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null,SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
connection.Close();
connection.Dispose();
return myReader;
}
catch(System.Data.SqlClient.SqlException e)
{
connection.Close();
connection.Dispose();
throw new Exception(e.Message);
}
// finally
// {
// cmd.Dispose();
// connection.Close();
// }
}
public string[] verifyReader(string query, string ColumnNumber1, string ColumnNumber2 = "0", string ColumnNumber3 = "0", string ColumnNumber4 = "0", string ColumnNumber5 = "0", string ColumnNumber6 = "0", string ColumnNumber7 = "0", string ColumnNumber8 = "0", string ColumnNumber9 = "0", string ColumnNumber10 = "0", string ColumnNumber11 = "0", string ColumnNumber12 = "0", string ColumnNumber13 = "0", string ColumnNumber14 = "0", string ColumnNumber15 = "0", string ColumnNumber16 = "0", string ColumnNumber17 = "0", string ColumnNumber18 = "0", string ColumnNumber19 = "0", string ColumnNumber20 = "0", string ColumnNumber21 = "0", string ColumnNumber22 = "0", string ColumnNumber23 = "0", string ColumnNumber24 = "0", string ColumnNumber25 = "0", string ColumnNumber26 = "0", string ColumnNumber27 = "0", string ColumnNumber28 = "0", string ColumnNumber29 = "0", string ColumnNumber30 = "0", string ColumnNumber31 = "0", string ColumnNumber32 = "0", string ColumnNumber33 = "0", string ColumnNumber34 = "0", string ColumnNumber35 = "0", string ColumnNumber36 = "0", string ColumnNumber37 = "0", string ColumnNumber38 = "0", string ColumnNumber39 = "0", string ColumnNumber40 = "0", string ColumnNumber41 = "0", string ColumnNumber42 = "0", string ColumnNumber43 = "0", string ColumnNumber44 = "0", string ColumnNumber45 = "0", string ColumnNumber46 = "0", string ColumnNumber47 = "0", string ColumnNumber48 = "0", string ColumnNumber49 = "0", string ColumnNumber50 = "0")
{
int i = 0;
string[] x = new string[50];
string[] ColumnArray = new string[50];
ColumnArray[0] = ColumnNumber1; ColumnArray[1] = ColumnNumber2; ColumnArray[2] = ColumnNumber3; ColumnArray[3] = ColumnNumber4; ColumnArray[4] = ColumnNumber5; ColumnArray[5] = ColumnNumber6; ColumnArray[6] = ColumnNumber7; ColumnArray[7] = ColumnNumber8; ColumnArray[8] = ColumnNumber9; ColumnArray[9] = ColumnNumber10; ColumnArray[10] = ColumnNumber11; ColumnArray[11] = ColumnNumber12; ColumnArray[12] = ColumnNumber13; ColumnArray[13] = ColumnNumber14; ColumnArray[14] = ColumnNumber15; ColumnArray[15] = ColumnNumber16; ColumnArray[16] = ColumnNumber17; ColumnArray[17] = ColumnNumber18; ColumnArray[18] = ColumnNumber19; ColumnArray[19] = ColumnNumber20; ColumnArray[20] = ColumnNumber21; ColumnArray[21] = ColumnNumber22; ColumnArray[22] = ColumnNumber23; ColumnArray[23] = ColumnNumber24; ColumnArray[24] = ColumnNumber25; ColumnArray[25] = ColumnNumber26; ColumnArray[26] = ColumnNumber27; ColumnArray[27] = ColumnNumber28; ColumnArray[28] = ColumnNumber29; ColumnArray[29] = ColumnNumber30; ColumnArray[30] = ColumnNumber31; ColumnArray[31] = ColumnNumber32; ColumnArray[32] = ColumnNumber33; ColumnArray[33] = ColumnNumber34; ColumnArray[34] = ColumnNumber35; ColumnArray[35] = ColumnNumber36; ColumnArray[36] = ColumnNumber37; ColumnArray[37] = ColumnNumber38; ColumnArray[38] = ColumnNumber39; ColumnArray[39] = ColumnNumber40; ColumnArray[40] = ColumnNumber41; ColumnArray[41] = ColumnNumber42; ColumnArray[42] = ColumnNumber43; ColumnArray[43] = ColumnNumber44; ColumnArray[44] = ColumnNumber45; ColumnArray[45] = ColumnNumber46; ColumnArray[46] = ColumnNumber47; ColumnArray[47] = ColumnNumber48; ColumnArray[48] = ColumnNumber49; ColumnArray[49] = ColumnNumber50;
System.Data.SqlClient.SqlDataReader reader;
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, cn);
cn.Open();
reader = cmd.ExecuteReader();
try{ while (reader.Read())
{
try{ while (i < 50)
{
try{ x[i] = reader[ColumnArray[i].ToString()].ToString().Trim(); }catch { if (ColumnArray[i].ToString() != "0")
{
x[i] = "0";
}
else
{
reader.Close(); cn.Close(); cn.Dispose(); break;
} } i++;
}
reader.Close(); cn.Close(); cn.Dispose(); return(x); }catch { reader.Close(); cn.Close(); cn.Dispose(); return(x); }
}
return(x); }
catch { reader.Close(); cn.Close(); cn.Dispose(); return(x); }
}
public DataTable BindoboutGrid(string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 1000;
cmd.Connection = cn;
try
{
cn.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
cn.Close();
cn.Dispose();
}
catch (Exception e) { }
finally
{
cn.Close();
sda.Dispose();
cn.Dispose();
}
// rows = dt.Rows.Count + " records matching your criteria.";
return(dt);
}
public static void ExecuteMyQuery(string Query)
{
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["constrww"].ToString();
SqlConnection Connection = new SqlConnection(conString);
Connection.Open();
SqlCommand cmdXls = new SqlCommand(Query, Connection);
try
{
cmdXls.ExecuteNonQuery();
}
catch (Exception e)
{
cmdXls.Dispose();
Connection.Dispose();
}
finally
{
cmdXls.Dispose();
Connection.Dispose();
}
}
public static bool AddTaskReplyAttachments(string taskid, string replyid, List<Attachment> attachments,string userid,string clientid)
{
SqlConnection conn = new SqlConnection(TaskDAL.ConnectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
foreach (var attachment in attachments)
{
if (!TaskDAL.BaseProvider.AddTaskReplyAttachment(taskid,replyid,attachment.Type,
attachment.ServerUrl,attachment.FilePath,attachment.FileName,attachment.OriginalName,attachment.ThumbnailName,attachment.Size,
userid,clientid, tran))
{
tran.Rollback();
conn.Dispose();
return false;
}
}
tran.Commit();
conn.Dispose();
return true;
}
public DataTable getAModel(string connectionString, int modelID)
{
DataTable results = new DataTable();
try
{
conn = DBUtility.createConnection(connectionString);
SqlCommand cmdIns = DBUtility.PrepareReadCommand("GetAModel", conn);
cmdIns.Parameters.Add(new SqlParameter("@Model_ID", modelID));
using (conn)
using (cmdIns)
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdIns))
{
dataAdapter.Fill(results);
}
cmdIns.Dispose();
DBUtility.Close(conn);
conn.Dispose();
return results;
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
DBUtility.Close(conn);
conn.Dispose();
}
}
protected void btnBookFlight_Click(object sender, EventArgs e)
{
SqlConnection objCon = new SqlConnection(strCon);
try
{
objCon.ConnectionString = strCon;
objCon.Open();
}
catch (Exception ex)
{
this.lblConnect.Text = "Unable to connect to database.";
}
if (objCon.State == System.Data.ConnectionState.Open)
{
InsertFlightInfo();
InsertFlightOrder();
objCon.Close();
objCon.Dispose();
}
else
{
lblConnect.Text = "Unable to connect to database.";
objCon.Close();
objCon.Dispose();
}
objCon.Close();
objCon.Dispose();
redirectToNextPage();
}
public DataTable getAllVehicles(string connectionString)
{
DataTable results = new DataTable();
try
{
conn = DBUtility.createConnection(connectionString);
SqlCommand cmdIns = DBUtility.PrepareReadCommand("sp_GetAllVehicles", conn);
using (conn)
using (cmdIns)
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdIns))
{
dataAdapter.Fill(results);
}
cmdIns.Dispose();
DBUtility.Close(conn);
conn.Dispose();
return results;
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
DBUtility.Close(conn);
conn.Dispose();
}
}
public System.Data.DataTable sdatatable(string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cn;
try
{
cn.Open();
sda.SelectCommand = cmd;
try
{
sda.Fill(dt);
}
catch { }
cn.Close();
cn.Dispose();
}
finally
{
cn.Close();
sda.Dispose();
cn.Dispose();
}
return(dt);
}
protected void InsertKeyIntoConfNumTable(string confnum)
{
string strCon = "Data Source=(local);Initial Catalog=FlightDB;Integrated Security=True";
SqlConnection objCon = new SqlConnection(strCon);
try
{
objCon.ConnectionString = strCon;
objCon.Open();
}
catch (Exception ex)
{
this.lblConnect.Text = "Unable to connect to database.";
}
if (objCon.State == System.Data.ConnectionState.Open)
{
string sql = "INSERT INTO flightconfirmation (confnum) VALUES ('" + confnum + "')";
SqlCommand cmd = new SqlCommand(sql, objCon);
cmd.ExecuteNonQuery();
objCon.Close();
objCon.Dispose();
}
else
{
lblConnect.Text = "Unable to connect to database.";
objCon.Close();
objCon.Dispose();
}
objCon.Close();
objCon.Dispose();
}
public void insert(UserObject userObj, string connectionString)
{
try
{
conn = DBUtility.createConnection(connectionString);
SqlCommand cmdIns = DBUtility.PrepareSPCommand("sp_InsertCompany", insertParameters(userObj), conn);
cmdIns.ExecuteNonQuery();
// cmdIns.Parameters.Clear();
// cmdIns.CommandText = "SELECT @@IDENTITY";
// // Get the last inserted id.
// int insertID = Convert.ToInt32(cmdIns.ExecuteScalar());
cmdIns.Dispose();
DBUtility.Close(conn);
conn.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
DBUtility.Close(conn);
conn.Dispose();
}
}
public void BindGrid1(GridView g, string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 8000;
cmd.Connection = cn;
try
{
cn.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
g.DataSource = dt;
g.AllowPaging = true;
g.DataBind();
cn.Close();
cn.Dispose();
}
finally
{
cn.Close();
sda.Dispose();
cn.Dispose();
}
}
protected void GC(object sender, EventArgs e)
{
if (con != null)
{
con.Close();
con.Dispose();
con = null;
}
}
public List<ActividadBE> GetActividades(string periodo)
{
SqlConnection Conn = null;
String sCadenaConeccion;
String sqlUsuarioObtener;
SqlCommand cmdUsuarioObtener = null;
SqlDataReader drUsuarioObtener;
SqlParameter prmCiclo;
try
{
sCadenaConeccion = Settings.Default.csDesarrollo;
Conn = new SqlConnection(sCadenaConeccion);
sqlUsuarioObtener = "usps_Actividad";
cmdUsuarioObtener = Conn.CreateCommand();
cmdUsuarioObtener.CommandText = sqlUsuarioObtener;
cmdUsuarioObtener.CommandType = CommandType.StoredProcedure;
prmCiclo = cmdUsuarioObtener.CreateParameter();
prmCiclo.ParameterName = "@ciclo";
prmCiclo.SqlDbType = SqlDbType.VarChar;
prmCiclo.Value = periodo;
cmdUsuarioObtener.Parameters.Add(prmCiclo);
cmdUsuarioObtener.Connection.Open();
drUsuarioObtener = cmdUsuarioObtener.ExecuteReader();
ActividadBE actividad;
List<ActividadBE> lstactividad = new List<ActividadBE>();
while (drUsuarioObtener.Read())
{
actividad = new ActividadBE();
actividad.id = drUsuarioObtener.GetInt32(drUsuarioObtener.GetOrdinal("id"));
actividad.mensaje = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("mensaje"));
actividad.fecha = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("fecha"));
actividad.titulo = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("titulo"));
actividad.ciclo = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("ciclo"));
lstactividad.Add(actividad);
}
cmdUsuarioObtener.Connection.Close();
cmdUsuarioObtener.Dispose();
Conn.Dispose();
return lstactividad;
}
catch (Exception ex)
{
cmdUsuarioObtener.Dispose();
Conn.Dispose();
throw;
}
}
public string ExecuteQuery(string query)
{
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, cn);
try { cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cn.Dispose(); return("Success"); }
catch (System.Data.SqlClient.SqlException sql) { return(sql.Message.ToString()); }
catch (Exception ex) { return(ex.Message.ToString()); }
finally { cn.Close(); cn.Dispose(); }
}
public decimal Peso_Uno(string codigo)
{
SqlConnection Conn = null;
String sCadenaConeccion;
String sqlUsuarioObtener;
SqlCommand cmdUsuarioObtener = null;
SqlDataReader drUsuarioObtener;
SqlParameter prmCodAlumno;
try
{
PromedioxCursoBE Promedio;
sCadenaConeccion = Settings.Default.csDesarrollo;
Conn = new SqlConnection(sCadenaConeccion);
sqlUsuarioObtener = "usps_PesoCurso";
cmdUsuarioObtener = Conn.CreateCommand();
cmdUsuarioObtener.CommandText = sqlUsuarioObtener;
cmdUsuarioObtener.CommandType = CommandType.StoredProcedure;
prmCodAlumno = cmdUsuarioObtener.CreateParameter();
prmCodAlumno.ParameterName = "@Curso_id";
prmCodAlumno.SqlDbType = SqlDbType.Int;
prmCodAlumno.Value = Convert.ToInt32(codigo);
cmdUsuarioObtener.Parameters.Add(prmCodAlumno);
cmdUsuarioObtener.Connection.Open();
drUsuarioObtener = cmdUsuarioObtener.ExecuteReader();
Promedio = new PromedioxCursoBE();
decimal promedio = 0;
while (drUsuarioObtener.Read())
{
promedio = Math.Round(drUsuarioObtener.GetDecimal(drUsuarioObtener.GetOrdinal("promedio")), 2);
}
cmdUsuarioObtener.Connection.Close();
cmdUsuarioObtener.Dispose();
Conn.Dispose();
return promedio;
}
catch (Exception ex)
{
cmdUsuarioObtener.Dispose();
Conn.Dispose();
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Cookies["VC"] != null)
{
string VC = Request.Cookies["VC"].Values["VC"];
Classes.LoginSession ls = new Classes.LoginSession();
int UserId = ls.getUserId(VC);
if (UserId == 0)
{
Response.Redirect("~/Login/Requests");
}
else
{
Session["UserId"] = UserId.ToString();
}
}
else
{
Response.Redirect("~/Login/Requests");
}
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProfilesConnectionString"].ConnectionString);
SqlCommand sqlCmd = new SqlCommand("sp_requestsAllRead", sqlConn);
try
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = Convert.ToInt32(Session["UserId"]);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
sqlConn.Close();
sqlCmd.Dispose();
sqlConn.Dispose();
}
sqlCmd.Dispose();
sqlConn.Dispose();
if (GridViewFriends.Rows.Count == 0)
{
LabelFriendsNoRecord.Visible = true;
}
}
public List<PeriodoBE> GetPeriodo()
{
SqlConnection Conn = null;
String sCadenaConeccion;
String sqlUsuarioObtener;
SqlCommand cmdUsuarioObtener = null;
SqlDataReader drUsuarioObtener;
try
{
List<PeriodoBE> Periodos = new List<PeriodoBE>();
PeriodoBE Periodo;
sCadenaConeccion = Properties.Settings.Default.csDesarrollo;
Conn = new SqlConnection(sCadenaConeccion);
sqlUsuarioObtener = "UMOBILE.sp_Periodo";
cmdUsuarioObtener = Conn.CreateCommand();
cmdUsuarioObtener.CommandText = sqlUsuarioObtener;
cmdUsuarioObtener.CommandType = CommandType.StoredProcedure;
cmdUsuarioObtener.Connection.Open();
drUsuarioObtener = cmdUsuarioObtener.ExecuteReader();
while (drUsuarioObtener.Read())
{
Periodo = new PeriodoBE();
Periodo.Id = drUsuarioObtener.GetInt32(drUsuarioObtener.GetOrdinal("id"));
Periodo.Periodo = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("periodo"));
Periodo.Inicio = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("inicio"));
Periodo.Fin = drUsuarioObtener.GetString(drUsuarioObtener.GetOrdinal("fin"));
Periodos.Add(Periodo);
}
cmdUsuarioObtener.Connection.Close();
cmdUsuarioObtener.Dispose();
Conn.Dispose();
return Periodos;
}
catch (Exception ex)
{
cmdUsuarioObtener.Dispose();
Conn.Dispose();
throw;
}
}
protected void btnOpen_Click(object sender, EventArgs e)
{
string dbName = tbDBName.Text;
SqlConnection con = new SqlConnection();
con.StateChange += con_StateChange;
con.InfoMessage += con_InfoMessage;
try
{
con.ConnectionString = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;
con.Open();
}
catch (SqlException ex)
{
lbLog.Items.Add(ex.Message);
}
catch (Exception ex)
{
lbLog.Items.Add(ex.Message);
}
finally
{
con.Dispose();
}
}
/// <summary>
/// 查询操作方法
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="paras">传入的参数</param>
/// <returns>返回执行结果的首行首列</returns>
public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] paras)
{
using (SqlConnection conn = new SqlConnection(serverPath))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
cmd.CommandType = cmdType;
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
conn.Close();
conn.Dispose();
throw ex;
}
}
}
}
public void DeleteAdmin(String id, ref List<string> errors)
{
var conn = new SqlConnection(ConnectionString);
try
{
var adapter = new SqlDataAdapter(DeleteAdminProcedure, conn)
{
SelectCommand =
{
CommandType = CommandType.StoredProcedure
}
};
adapter.SelectCommand.Parameters.Add(new SqlParameter("@admin_id", SqlDbType.VarChar, 20));
adapter.SelectCommand.Parameters["@admin_id"].Value = id;
var dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (Exception e)
{
errors.Add("Error: " + e);
}
finally
{
conn.Dispose();
}
}
public string[] xread(string query, string[] columns) /*For Select*/
{
string[] x = new string[columns.Length];
var cn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString);
var cmd = new System.Data.SqlClient.SqlCommand(query, cn);
cn.Open();
var reader = cmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
try
{
for (i = 0; i < columns.Length; i++)
{
x[i] = reader[columns[i].ToString()].ToString().Trim();
}
}
catch
{
x[i] = "N/A";
}
}
reader.Close(); cmd.Dispose(); cn.Close(); cn.Dispose();
return(x);
}
protected void Page_Load(object sender, EventArgs e)
{
//check login status
CheckLogin cl = new CheckLogin();
bool LoginStatus = cl.checkLogin(Convert.ToInt32(Session["UserId"]));
if (!LoginStatus)
{
Response.Redirect("Login.aspx?Page=Requests");
}
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ShopConnectionString"].ConnectionString);
SqlCommand sqlCmd = new SqlCommand("sp_requestsAllRead", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = Convert.ToInt32(Session["UserId"]);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
sqlConn.Dispose();
if (GridViewFriends.Rows.Count == 0)
{
LabelFriendsNoRecord.Visible = true;
}
}
public void BindListboxWithValue(ListBox dd, string query)
{
dd.Items.Clear();
string connnecstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connnecstring);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, cn);
string value = ""; int i = 0;
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
value = value + reader[0] + ":" + reader[1] + ",";
}
reader.Close(); reader.Dispose(); cn.Close(); cn.Dispose();
string[] split = Regex.Split(value, ",");
while (i < (split.Length - 1))
{
string[] split1 = Regex.Split(split[i], ":");
dd.Items.Add(new ListItem(split1[0], split1[1]));
i++;
}
}
/// <summary>
/// EF SQL 语句返回 DataSet
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataSet SqlQueryForDataSet(Database db,
string sql)
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
//conn.ConnectionString = db.Connection.ConnectionString;
//if (conn.State != ConnectionState.Open)
//{
// conn.Open();
//}
conn = (SqlConnection)db.Connection;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
conn.Close();//连接需要关闭
conn.Dispose();
return(ds);
}
/// <summary>
/// Inserir stop start da ordem na tabela tbStopStartOrder
/// </summary>
/// <param name="pOrdem"></param>
/// <returns></returns>
public virtual int EnviarOrdemStop(AutomacaoOrdensInfo pOrdem)
{
SqlCommand command = new SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = Conn;
conn.Open();
try
{
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "PRC_INS_ORDEM_STOP_OMS";
SqlParameter OutParameter = new SqlParameter();
OutParameter.Direction = ParameterDirection.Output;
OutParameter.ParameterName = "@StopStartID";
OutParameter.Size = 32;
command.Parameters.Add(OutParameter);
command.Parameters.AddWithValue("@OrdTypeID", pOrdem.OrdTypeID);
command.Parameters.AddWithValue("@StopStartStatusID", pOrdem.StopStartStatusID);
command.Parameters.AddWithValue("@Symbol", pOrdem.Symbol);
command.Parameters.AddWithValue("@OrderQty", pOrdem.OrderQty);
command.Parameters.AddWithValue("@Account", pOrdem.Account);
//command.Parameters.AddWithValue("@RegisterTime", pOrdem.RegisterTime);
command.Parameters.AddWithValue("@ExpireDate", pOrdem.ExpireDate);
command.Parameters.AddWithValue("@ExecutionTime", pOrdem.ExecutionTime);
command.Parameters.AddWithValue("@ReferencePrice", pOrdem.ReferencePrice);
command.Parameters.AddWithValue("@StartPriceValue", pOrdem.StartPriceValue);
command.Parameters.AddWithValue("@SendStartPrice", pOrdem.SendStartPrice);
command.Parameters.AddWithValue("@StopGainValuePrice", pOrdem.StopGainValuePrice);
command.Parameters.AddWithValue("@SendStopGainPrice", pOrdem.SendStopGainPrice);
command.Parameters.AddWithValue("@StopLossValuePrice", pOrdem.StopLossValuePrice);
command.Parameters.AddWithValue("@SendStopLossValuePrice", pOrdem.SendStopLossValuePrice);
command.Parameters.AddWithValue("@InitialMovelPrice", pOrdem.InitialMovelPrice);
command.Parameters.AddWithValue("@AdjustmentMovelPrice", pOrdem.AdjustmentMovelPrice);
command.Parameters.AddWithValue("@StopStartTipoEnum", (int)pOrdem.IdStopStartTipo);
command.Parameters.AddWithValue("@PortaControle", pOrdem.ControlePorta);
command.ExecuteNonQuery();
int id_stopstart = int.Parse(command.Parameters["@StopStartID"].Value.ToString());
return(id_stopstart);
}
catch (Exception ex)
{
throw new Exception(string.Format("{0}{1}", "EnviarOrdemStop: ", ex.Message));
}
finally
{
conn.Close();
conn.Dispose();
command.Connection.Close();
command.Dispose();
command = null;
}
}
} //{0 city/district 1 means tehsil/TALUKA
public List <City> GetAllByState(int StateId)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
List <City> ListTmp = new List <City>();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM City where StateId=" + StateId.ToString() + " ORDER BY Name ASC";
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
City ObjTmp = new City
{
CityId = int.Parse(SDR["CityId"].ToString()),
Name = SDR["Name"].ToString(),
StateId = int.Parse(SDR["StateId"].ToString()),
Type = int.Parse(SDR["Type"].ToString())
};
ListTmp.Add(ObjTmp);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
public static int ExecuteSql(string sql, string conStr)
{
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr))
{
try
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(sql, conn);
foreach (var sqlBatch in sql.Split(new[] { "GO", "go" }, StringSplitOptions.RemoveEmptyEntries))
{
sqlCommand.CommandText = sqlBatch;
sqlCommand.ExecuteNonQuery();
}
return(1);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally{
conn.Close();
conn.Dispose();
}
}
}
public decimal GetScalarDecimal(string sql)
{
decimal x = 0.0M;
System.Data.SqlClient.SqlConnection conn3 = new System.Data.SqlClient.SqlConnection(cnStr);
conn3.Open();
SqlCommand cmd = new SqlCommand(sql, conn3);
object result = cmd.ExecuteScalar();
if (result == null)
{
return(-1.0M);
}
try
{
x = (decimal)result;
}
catch
{
x = -1.0M;
}
conn3.Close();
conn3.Dispose();
conn3 = null;
return(x);
}
public string GetScalarString(string sql)
{
string x = "";
System.Data.SqlClient.SqlConnection conn3 = new System.Data.SqlClient.SqlConnection(cnStr);
conn3.Open();
SqlCommand cmd = new SqlCommand(sql, conn3);
object result = cmd.ExecuteScalar();
if (result == null)
{
return("");
}
if (result.ToString() == "")
{
x = "";
}
else
{
x = (string)result;
}
conn3.Close();
conn3.Dispose();
conn3 = null;
return(x);
}
public System.Collections.Generic.List <UsersMenu> GetAll()
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
System.Collections.Generic.List <UsersMenu> ListTmp = new System.Collections.Generic.List <UsersMenu>();
UsersMenu ObjTmp = null;
SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM Menu ORDER BY Menu_Id ASC";
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp = new UsersMenu
{
Menu_Id = int.Parse(SDR["Menu_Id"].ToString()),
MenuDisplayName = SDR["MenuDisplayName"].ToString(),
ParentMenuId = int.Parse(SDR["ParentMenuId"].ToString()),
MenuOrderNo = int.Parse(SDR["MenuOrderNo"].ToString()),
MenuLink = SDR["MenuLink"].ToString(),
Menu_Icon = SDR["Menu_Icon"].ToString(),
User_Types = SDR["User_Types"].ToString(),
Section = SDR["Section"].ToString()
};
ListTmp.Add(ObjTmp);
}
}
catch (System.Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
public static string GetProcessId(ref string ProcessId, string TenderNumber)
{
SqlConnection sqlConn = new SqlConnection(); //defines database connection
SqlCommand sqlCmd = new SqlCommand(); //defines what to do
SqlDataAdapter sqlAdap = new SqlDataAdapter();
try
{
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
sqlConn.Open();
sqlCmd.CommandText = "SELECT [TH_ProcessID] FROM [AMS_Master].[dbo].[Tender_Header] H WHERE [TH_NoTender] = '" + TenderNumber + "'";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlConn;
ProcessId = sqlCmd.ExecuteScalar().ToString();
return string.Empty;
}
catch (Exception err)
{
return err.Message;
}
finally
{ sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
}
public City GetOne(int Cityid)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
City ObjTmp = new City();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT TOP 1 * FROM City where CityId=" + Cityid.ToString();
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
ObjTmp.CityId = int.Parse(SDR["CityId"].ToString());
ObjTmp.Name = SDR["Name"].ToString();
ObjTmp.StateId = int.Parse(SDR["StateId"].ToString());
ObjTmp.Type = int.Parse(SDR["Type"].ToString());
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ObjTmp);
}
public static void ExecuteNonResult(string sql)
{
try
{
//string connString = "Data Source=.\\SQLExpress; Integrated Security=true; User Instance=true; Initial Catalog=master;";
string connString = "Server=.\\SQLEXPRESS;Database=master;Integrated Security=SSPI;";
System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
Logger.LogQuery("SQL Exception:" + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine + ex.ToString(), true, true);
throw;
}
}
protected void btnAgregar_Click(object sender, System.EventArgs e)
{
string correo_electronico = txtCorreo.Text.Trim();
if (correo_electronico.Length < 3)
{
return;
}
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString;
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
try
{
string strQuery = "INSERT INTO [matricula].[SGPermisos] (usuario, certificado, correo, ficha, sistema, sincronizacion, actualizacion) VALUES ('" + correo_electronico + "', 2, 2, 2, 2, 2, getdate());";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strQuery, connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (System.Exception e1)
{
System.Console.WriteLine("Ocurri\u00F3 un error al intentar ingresar un permiso de usuario. " + e1.Message);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
txtCorreo.Text = "";
cargarPermisos();
}
public System.Data.DataTable GetData(System.Data.SqlClient.SqlCommand cmd)
{
System.Data.DataTable dt = new System.Data.DataTable();
string strConnString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = global::System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;
System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
cmd.CommandTimeout = 1000;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return(dt);
}
catch (Exception ex)
{
return(dt);
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
public List <District> GetAllByStsCity(int StateId, int CityId, bool All = false)
{
System.Data.SqlClient.SqlCommand cmd = null;
System.Data.SqlClient.SqlDataReader SDR = null;
List <District> ListTmp = new List <District>();
System.Data.SqlClient.SqlConnection Con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ToString());
Con.Open();
try
{
string Query = "SELECT * FROM District where StateId=" + StateId.ToString() + " and CityId=" + CityId.ToString();
if (All)
{
Query = "SELECT * FROM District";
}
cmd = new System.Data.SqlClient.SqlCommand(Query, Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
District ObjTmp = new District
{
Id = int.Parse(SDR["Id"].ToString()),
Name = SDR["Name"].ToString(),
StateId = int.Parse(SDR["StateId"].ToString()),
CityId = int.Parse(SDR["CityId"].ToString()),
};
ListTmp.Add(ObjTmp);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); SDR.Close(); Con.Close(); Con.Dispose(); Con = null; }
return(ListTmp);
}
/// <summary>
/// 得到 DataSet
/// </summary>
/// <param name="SqlString"></param>
/// <param name="_DbParameter"></param>
/// <param name="_Action"></param>
/// <returns></returns>
public DataSet SqlQueryDataSet(string SqlString, DbParameter[] _DbParameter = null, Action <DbCommand> _Action = null)
{
SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();
_SqlConnection.ConnectionString = Context.Database.Connection.ConnectionString;
if (_SqlConnection.State != ConnectionState.Open)
{
_SqlConnection.Open();
}
SqlCommand _SqlCommand = new SqlCommand();
_SqlCommand.Connection = _SqlConnection;
_SqlCommand.CommandText = SqlString;
if (_DbParameter != null && _DbParameter.Length > 0)
{
foreach (var item in _DbParameter)
{
_SqlCommand.Parameters.Add(item);
}
}
_Action?.Invoke(_SqlCommand);
SqlDataAdapter _SqlDataAdapter = new SqlDataAdapter(_SqlCommand);
var _DataSet = new DataSet();
_SqlDataAdapter.Fill(_DataSet);
_SqlConnection.Close();//连接需要关闭
_SqlConnection.Dispose();
return(_DataSet);
}
/// <summary>
/// 使用原始sql语句生成DataTable（仅支持SQLServer)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数列表</param>
/// <returns>返回DataTable</returns>
public DataTable GetDataTable(string sql, params object[] parameters)
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = _db.DataBase.Connection.ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
foreach (var p in parameters)
{
cmd.Parameters.Add(p);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
conn.Close();//连接需要关闭
conn.Dispose();
return(table);
}
public static void DeleteOneDto(string table, string strwhere)
{
SqlParameter[] arParames = new SqlParameter[2];
arParames[0] = new SqlParameter("@table ", SqlDbType.VarChar, 200);
arParames[0].Value = table;
arParames[1] = new SqlParameter("@Where ", SqlDbType.VarChar, 8000);
arParames[1].Value = strwhere;
SqlConnection myconn = new SqlConnection(CommonDal.ConnectionString);
try
{
SqlHelper.ExecuteNonQuery(myconn, CommandType.StoredProcedure, "deleteModelByWhere", arParames);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
myconn.Close();
myconn.Dispose();
}
}
/// <summary>
/// 使用select取得DataTable
/// </summary>
/// <returns></returns>
public System.Data.DataTable SelectDataTable()
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(this.ConnectionString);
conn.Open();
System.Data.DataTable tb = new System.Data.DataTable();
try
{
comm.Connection = conn;
comm.CommandText = SelectString;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(comm);
da.Fill(tb);
da.Dispose();
return(tb);
}
catch
{
return(tb);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public ActionResult ClassExistProduct(int class_appser)
{
string msg;
// 1. 資料庫連線
SqlConnection cn = new SqlConnection(_connectionString);
// 2. SQL 指令
string sql = "select COUNT(*) from product_class a join Product b on a.app_ser=b.prod_class_id where a.app_ser=@id";
cn.Open();
SqlCommand comm = new SqlCommand(sql, cn);
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@id", class_appser);
if ((int)comm.ExecuteScalar() > 0)
{
msg = "Exist";
}
else
{
msg = "OK";
}
cn.Close();
cn.Dispose();
var result = new
{
message = msg
};
return Json(result, JsonRequestBehavior.AllowGet);
//string obj_json = JsonConvert.SerializeObject(result);
//return Content(obj_json, "application/json");
}
void Duyurular()
{
SqlConnection con = new System.Data.SqlClient.SqlConnection("Data Source=sql2012.isimtescil.net;Initial Catalog=hitityazilim_db9999;Persist Security Info=True;User ID=hitityazilim_adem;Password=Hitit9999");
string sql = "select * from Duyurular with(nolock)";
//sql = sql.Replace("@KullaniciAdi", username);
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable();
try
{
adp.Fill(dt);
}
catch (Exception exp)
{
return;
}
finally
{
con.Dispose();
adp.Dispose();
}
gridControl1.DataSource = dt;
//if (dt.Rows.Count == 0)
//{
// XtraMessageBox.Show("Lisans için Lütfen Yazılım firmasını arayınız.", Degerler.mesajbaslik, MessageBoxButtons.OK, MessageBoxIcon.Stop);
// DB.kayitli = 0;
// return;
//}
}
// To insert 'SubMenu' record in database by stored procedure
public int InsertSubMenu(int MenuId, string SubMenuName, string SubMenuDesc, string SubMenuURL,int IsActive,int LoggedInUser, string RetMsg)
{
SqlConnection Conn = new SqlConnection(ConnString);
Conn.Open();
// 'uspInsertSubMenu' stored procedure is used to insert record in SubMenu table
SqlCommand Cmd = new SqlCommand("uspInsertSubMenu", Conn);
Cmd.CommandType = CommandType.StoredProcedure;
try
{
Cmd.Parameters.AddWithValue("@MenuID", MenuId);
Cmd.Parameters.AddWithValue("@SubMenuName", SubMenuName);
Cmd.Parameters.AddWithValue("@SubMenuDescription", SubMenuDesc);
Cmd.Parameters.AddWithValue("@SubMenuURL", SubMenuURL);
Cmd.Parameters.AddWithValue("@IsActive", IsActive);
Cmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
Cmd.Parameters.AddWithValue("@RetMsg", RetMsg);
return Cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
Cmd.Dispose();
Conn.Close();
Conn.Dispose();
}
}
/// <summary>
/// 执行一条无返回值的SQL命令（不带事务）
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="sqlCommand">SQL命令</param>
/// <returns></returns>
public virtual int ExecuteNonQuery(string ConnStr, string sqlCommand)
{
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(sqlCommand, sqlconn))
{
return(sqlcmd.ExecuteNonQuery());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
// To Change status of 'Group' record of specific GroupId from database by stored procedure
public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive)
{
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
// 'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table
SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn);
DCmd.CommandType = CommandType.StoredProcedure;
DCmd.Parameters.AddWithValue("@GroupId", GroupId);
DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
DCmd.Parameters.AddWithValue("@IsActive", IsActive);
DCmd.Parameters.AddWithValue("@RetMsg", returnmsg);
try
{
return DCmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
DCmd.Dispose();
Conn.Close();
Conn.Dispose();
}
}
/// <summary>
/// 执行oracle 数据库普通查询 ，返回table
/// </summary>
/// <param name="oraquery"></param>
/// <returns></returns>
public static DataTable GetCommonQuery(string cmdStr)
{
using (DBContext db = new DBContext())
{
DataTable table = new DataTable();
if (cmdStr != null && cmdStr.Length > 0)
{
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn = (SqlConnection)db.Database.Connection;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = cmdStr;
//执行填充Table
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);
conn.Close();//连接需要关闭
conn.Dispose();
}
return(table);
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string fileext = System.IO.Path.GetExtension(FileUpload1.FileName);
if (fileext == ".jpg" || fileext == ".gif" || fileext == ".png")
{
int Size = FileUpload1.PostedFile.ContentLength;
FileUpload1.SaveAs(Server.MapPath("TravelImages") + "\\" + FileUpload1.FileName);
string FileName = System.IO.Path.GetFileName(FileUpload1.FileName);
string Folder = "~/First system/Image/";
string FileUrl = Folder + FileName;
SqlConnection conn = new SqlConnection("server=172.16.101.46;database=SysAuction;uid=sa;pwd=zjj1024455257");
conn.Open();
string myinsert = "update [User] set headpicture ='Image/headpic.jpg' where Username='******'";
SqlCommand mycom = new SqlCommand(myinsert, conn);
mycom.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
}
catch
{
}
}
/// <summary>
/// 返回一行一列的数据
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL语句</param>
/// <returns></returns>
public virtual object GetScalarData(string ConnStr, string strSQL)
{
{
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
return(sqlcmd.ExecuteScalar());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
}
/// <summary>
/// Atualiza stop start da ordem na tabela tbStopStartOrder
/// </summary>
/// <param name="id_startstop">Id do Stop/Start </param>
/// <param name="id_stopstart_status">Id do status Stop/Start</param>
/// <param name="preco_referencia">Preço referencia</param>
public virtual void AtualizaOrdemStop(int id_startstop, int id_stopstart_status, decimal?preco_referencia)
{
SqlCommand command = new SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = Conn;
conn.Open();
try
{
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "prc_atualiza_ordem_stop";
command.Parameters.AddWithValue("@StopStartID", id_startstop);
command.Parameters.AddWithValue("@StopStartStatusID", id_stopstart_status);
command.Parameters.AddWithValue("@ReferencePrice", preco_referencia);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Error("AtualizaOrdemStop(): " + ex.Message, ex);
throw new Exception(string.Format("{0}{1}", "AtualizaOrdemStop: ", ex.Message), ex);
}
finally
{
conn.Close();
conn.Dispose();
command.Connection.Close();
command.Dispose();
command = null;
}
}
public static DataSet ExecuteQuery(string sql)
{
try
{
//string connString = "Data Source=.\\SQLExpress; Integrated Security=true; User Instance=true; Initial Catalog=master;";
string connString = "Server=.\\SQLEXPRESS;Database=master;Integrated Security=SSPI;";
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = connString; //DBBase.GetConnectionString();
con.Open();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
DataSet ds1 = new DataSet();
da.Fill(ds1);
da.Dispose();
con.Close();
con.Dispose();
return(ds1);
}
catch (Exception ex)
{
Logger.LogQuery("SQL Exception:" + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine + ex.ToString(), true, true);
throw;
}
}
protected void ButNewDept_Click(object sender, System.EventArgs e)
{
if (txtTestTypeName.Text.Trim()=="")
{
this.RegisterStartupScript("newWindow","<script language='javascript'>alert('�������Ʋ���Ϊ�գ�')</script>");
return;
}
string strTmp=ObjFun.GetValues("select TestTypeName from TestTypeInfo where TestTypeName='"+ObjFun.getStr(ObjFun.CheckString(txtTestTypeName.Text.Trim()),20)+"'","TestTypeName");
if (strTmp=="")
{
string strConn=ConfigurationSettings.AppSettings["strConn"];
SqlConnection SqlConn=new SqlConnection(strConn);
SqlCommand SqlCmd=new SqlCommand("Insert into TestTypeInfo(TestTypeName,BaseTestType) values('"+ObjFun.getStr(ObjFun.CheckString(txtTestTypeName.Text.Trim()),20)+"','"+DDLBaseTestType.SelectedItem.Value+"')",SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
SqlConn.Dispose();
this.RegisterStartupScript("newWindow","<script language='javascript'>alert('����������Ƴɹ���')</script>");
}
else
{
this.RegisterStartupScript("newWindow","<script language='javascript'>alert('�����������Ѿ����ڣ�')</script>");
return;
}
txtTestTypeName.Text="";
ShowData(strSql);
}
/// <summary>
/// Cancela Stop/Start da ordem
/// </summary>
/// <param name="id_startstop">Id do Stop/Start</param>
/// <param name="id_stopstart_status">Id do Status do stop</param>
public virtual void CancelaOrdemStopStart(int id_startstop, int id_stopstart_status)
{
SqlCommand command = new SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = Conn;
conn.Open();
try
{
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "prc_cancela_ordem_stop";
command.Parameters.AddWithValue("@StopStartID", id_startstop);
command.Parameters.AddWithValue("@StopStartStatusID", id_stopstart_status);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(string.Format("{0}{1}", "CancelaOrdemStopStart: ", ex.Message));
}
finally
{
conn.Close();
conn.Dispose();
command.Connection.Close();
command.Dispose();
command = null;
}
}
