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; } }