public static string InsUpdDel(string query) { SqlConnection sqlcon = new SqlConnection(connstring); SqlCommand sqlcom = new SqlCommand(query, sqlcon); //sqlcom.CommandType = CommandType.Text; sqlcon.Open(); try { // string connstring = ConfigurationManager.ConnectionStrings["connid"].ToString(); //ConfigurationManager.AppSettings["connid"]; sqlcom.ExecuteNonQuery(); query = ""; sqlcom.Dispose(); sqlcon.Close(); return "true"; } catch(Exception prajwol) { query = ""; sqlcom.Dispose(); sqlcon.Close(); return prajwol.Message.ToString(); } }
public DataSet FilterData(ArsonFilterModel filterData) { var connectionString = ConfigurationManager.ConnectionStrings["UCR_DataEntities"]; DataSet dsResult = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString)) { try { SqlCommand command = new SqlCommand(); command.Connection = conn; command.CommandText = GenerateArsonORQuery(filterData); command.CommandType = System.Data.CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; conn.Open(); adapter.Fill(dsResult); conn.Close(); adapter.Dispose(); command.Dispose(); } catch (Exception ex) { StorageClient.LogError(ex); } } return dsResult; }
public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu) { string str = ""; string sql = "InsertKH"; // string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)"; SqlConnection con = new SqlConnection(KetNoiDB.getconnect()); con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@TenKH", TenKH); cmd.Parameters.AddWithValue("@gt", Gioitinh); cmd.Parameters.AddWithValue("@DC", Diachi); cmd.Parameters.AddWithValue("@SDT", SDT); cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH); cmd.Parameters.AddWithValue("@ghichu", Ghichu); //cmd.ExecuteNonQuery(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); str = dt.Rows[0].ItemArray[0].ToString(); cmd.Dispose(); con.Close(); return str; }
public void LinkAbsenceWithLeaveRequest(List<ReportedAbsenceEntity> t, LeaveRequestEntity request) { SqlConnection conn = null; SqlCommand cmd = null; try { conn = DALHelper.CreateSqlDbConnection(); foreach (ReportedAbsenceEntity ae in t) { cmd = new SqlCommand("usp_LinkAbsenceWithLeaveRequest", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ReportedAbsenceId", ae.Id); cmd.Parameters.AddWithValue("@LeaveRequestId", request.Id); conn.Close(); cmd.Dispose(); cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } finally { conn.Close(); cmd.Dispose(); conn.Dispose(); } }
public static string connstring = ConfigurationManager.ConnectionStrings["connid"].ToString(); //ConfigurationManager.AppSettings["connid"]; public static string GetString(string query) { string retvalue = ""; SqlConnection sqlcon = new SqlConnection(connstring); SqlCommand sqlcom = new SqlCommand(query, sqlcon); //sqlcom.CommandType = CommandType.Text; sqlcon.Open(); SqlDataReader sqldr = sqlcom.ExecuteReader(); sqldr.Read(); try { retvalue = sqldr[0].ToString(); sqldr.Dispose(); sqlcom.Dispose(); sqlcon.Close(); } catch { sqldr.Dispose(); sqlcom.Dispose(); sqlcon.Close(); return ""; } return retvalue; }
private void AgregarReunion_Load(object sender, EventArgs e) { using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString)) { var cmd = new System.Data.SqlClient.SqlCommand( "select max(ReunionID) + 1 from Reuniones " , conn); try { conn.Open(); SqlDataReader Reader = cmd.ExecuteReader(); while (Reader.Read()) { txtReunionID.Text = Reader.GetInt32(0).ToString(); } } catch (Exception ex) { MessageBox.Show(ex.Message); cmd.Dispose(); conn.Close(); return; } cmd.Dispose(); conn.Close(); }//using }
public bool IsValid(string _username, string _password) { string npiconstring = ConfigurationManager.ConnectionStrings["npi_con_string"].ConnectionString; using (var cs = new SqlConnection(npiconstring)) { string _sql = @"SELECT [username] FROM [dbo].[System_Users]" + @"WHERE [username] = @u AND [password]= @p"; //Ejecutar comando var cmd = new SqlCommand(_sql, cs); cmd.Parameters .Add(new SqlParameter("@u", SqlDbType.NVarChar)) .Value = _username; cmd.Parameters .Add(new SqlParameter("@p", SqlDbType.NVarChar)) //.Value = Helpers.SHA1.Encode(_password); <--- Verificar porque no esta agarrando el encoding .Value = _password; //Abrir conexion cs.Open(); //Leer datos var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Dispose(); cmd.Dispose(); return true; } else { reader.Dispose(); cmd.Dispose(); return false; } } }
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(); } }
/// <summary> /// Checks if user with given password exists in the database /// </summary> /// <param name="_username">User name</param> /// <param name="_password">User password</param> /// <returns>True if user exist and password is correct</returns> public bool IsValid(string _username, string _password) { using (var cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename" + @"='C:\Tutorials\1 - Creating a custom user login form\Creating " + @"a custom user login form\App_Data\Database1.mdf';Integrated Security=True")) { string _sql = @"SELECT [Username] FROM [dbo].[System_Users] " + @"WHERE [Username] = @u AND [Password] = @p"; var cmd = new SqlCommand(_sql, cn); cmd.Parameters .Add(new SqlParameter("@u", SqlDbType.NVarChar)) .Value = _username; cmd.Parameters .Add(new SqlParameter("@p", SqlDbType.NVarChar)) .Value = Helpers.SHA1.Encode(_password); cn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Dispose(); cmd.Dispose(); return true; } else { reader.Dispose(); cmd.Dispose(); return false; } } }
private void btnGuardar_Click(object sender, EventArgs e) { using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString)) { var cmd = new System.Data.SqlClient.SqlCommand( " insert into Reuniones (ReunionID, Nombre, Lugar, FechaInicio, FecharTermino ) values ( @ReunionID, @Nombre, @Lugar, @FechaInicio, @FechaTermino)" , conn); try { cmd.Parameters.AddWithValue("@ReunionID", txtReunionID.Text); cmd.Parameters.AddWithValue("@Nombre", txtNombre.Text); cmd.Parameters.AddWithValue("@Lugar", txtLugar.Text); cmd.Parameters.Add("@FechaInicio", SqlDbType.DateTime).Value = dtpickerInicio.Value; cmd.Parameters.Add("@FechaTermino", SqlDbType.DateTime).Value = dtpickerFin.Value; conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); cmd.Dispose(); conn.Close(); } cmd.Dispose(); conn.Close(); } MessageBox.Show("Reunion Agregada"); Close(); }
private bool ExisteRegistro(int NumeroControl, int Reunion) { int Contador = 0; using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString)) { var cmd = new System.Data.SqlClient.SqlCommand( " select count (NumeroControl) as Cuantos from Asistencia where PersonaID = @NumeroControl and ReunionID = @ReunionID " , conn); try { cmd.Parameters.AddWithValue("@NumeroControl", NumeroControl); cmd.Parameters.AddWithValue("@ReunionID", Reunion); contador = Utilities.checkCount(conn, cmd); } catch (Exception ex) { MessageBox.Show(ex.Message); cmd.Dispose(); conn.Close(); return(false); } cmd.Dispose(); conn.Close(); }//using if (Contador > 0) { return(true); } else { return(false); } }
public District FindDistrict(string name) { string query = "SELECT * FROM tbl_district WHERE name='" + name + "'"; ASqlCommand = new SqlCommand(query, ASqlConnection); ASqlConnection.Open(); ASqlDataReader = ASqlCommand.ExecuteReader(); if (ASqlDataReader.HasRows) { District aDistrict = new District(); ASqlDataReader.Read(); aDistrict.Id = (int) ASqlDataReader["id"]; aDistrict.Name = ASqlDataReader["name"].ToString(); aDistrict.Population = (int) ASqlDataReader["population"]; ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return aDistrict; } else { ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return null; } }
public string methodregister(string userid, string firstname, string lastname, string pswrd, string sex, string streeet, string town, string stat, string zipcode, string phonenum, string que, string ans) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("Select * from userinfo where uid = '"+ userid +"'", conn); SqlDataReader read = cmd.ExecuteReader(); if( (read.HasRows)) { cmd.Dispose(); read.Close(); conn.Close(); return "fail"; } else { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1"].ConnectionString); con.Open(); SqlCommand c = new SqlCommand("Insert into userinfo(uid,fname,lname,phone,password,gender,street,city,state,zip,secQ,secAns) values ( '" + userid + "', '" + firstname + "','" + lastname + "','" + phonenum + "','" + pswrd + "', '" + sex + "','" + streeet + "','" + town + "','" + stat + "','" + zipcode + "','" + que + "','" + ans + "')", con); int n = c.ExecuteNonQuery(); cmd.Dispose(); c.Dispose(); read.Close(); con.Close(); conn.Close(); return ("success"); } }
public string log(string uname, string pwd) { //if (uname == "admin" && pwd == "admin") //{ // return "success"; //} //else //{ // return "fail"; //} SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbString"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("Select * from Table2 where Userid='" + uname + "' AND Password = '******' ", conn); SqlDataReader data = null; data = cmd.ExecuteReader(); if (data.HasRows) { data.Dispose(); cmd.Dispose(); conn.Close(); return "Y"; } else { data.Dispose(); cmd.Dispose(); conn.Close(); return "N"; } }
private bool RegistrarAsistencia() { using (var conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString)) { var cmd = new System.Data.SqlClient.SqlCommand( " insert into Asistencia (PersonaID,ReunionID,TomaAsistencia) values (@NumeroControl, @ReunionID, sysdatetime())" , conn); try { cmd.Parameters.AddWithValue("@NumeroControl", txtNumeroControl.Text); cmd.Parameters.AddWithValue("@ReunionID", cmbReuniones.SelectedValue); conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); cmd.Dispose(); conn.Close(); return(false); } cmd.Dispose(); conn.Close(); } return(true); }
/// <summary> /// Checks if user with given password exists in the database /// </summary> /// <param name="_username">User name</param> /// <param name="_password">User password</param> /// <returns>True if user exist and password is correct</returns> public bool IsValid(string _username, string _password) { using (var cn = new SqlConnection(@"Data Source=jeff.cedarville.edu; Persist Security Info=True;User ID=itm3500;Password=itm3500; MultipleActiveResultSets=True")) { string _sql = @"SELECT [Username] FROM [VanDintel-HarrisProject_Security].[System_Users] " + @"WHERE [Username] = @u AND [Password] = @p"; var cmd = new SqlCommand(_sql, cn); cmd.Parameters .Add(new SqlParameter("@u", SqlDbType.NVarChar)) .Value = _username; cmd.Parameters .Add(new SqlParameter("@p", SqlDbType.NVarChar)) .Value = System.Web.Helpers.Crypto.SHA1(_password); cn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Dispose(); cmd.Dispose(); return true; } else { reader.Dispose(); cmd.Dispose(); return false; } } }
public Patient Find(long voterId) { string query = "SELECT * FROM tbl_patient WHERE voter_id='" + voterId + "'"; ASqlCommand = new SqlCommand(query, ASqlConnection); ASqlConnection.Open(); ASqlDataReader = ASqlCommand.ExecuteReader(); if (ASqlDataReader.HasRows) { Patient aPatient = new Patient(); ASqlDataReader.Read(); aPatient.Id = (int)ASqlDataReader["id"]; aPatient.VoterId = (long)ASqlDataReader["voter_id"]; ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return aPatient; } else { ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return null; } }
public void ProcessRequest(HttpContext context) { if (!String.IsNullOrEmpty(context.Request.QueryString["term"].ToString())) { string connStr = ConfigurationManager.ConnectionStrings["DBCONNSTRING"].ToString(); SqlConnection sqlconn = new SqlConnection(connStr); SqlCommand sqlcmd = new SqlCommand(); try { if (sqlconn.State == ConnectionState.Closed) { sqlconn.Open(); } sqlcmd.Connection = sqlconn; sqlcmd.CommandType = CommandType.Text; sqlcmd.CommandText = "SELECT top 10 x.CountryName as cn FROM Countries as x WHERE x.CountryName LIKE '%' + @cn + '%'"; sqlcmd.Parameters.AddWithValue("@cn", context.Request.QueryString["term"].ToString()); sqlcmd.ExecuteNonQuery(); SqlDataAdapter da = new SqlDataAdapter(sqlcmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) { string[] items = new string[dt.Rows.Count]; int ctr = 0; foreach (DataRow row in dt.Rows) { items[ctr] = (string)row["cn"]; ctr++; } //convert the string array to Javascript and send it out context.Response.Write(new JavaScriptSerializer().Serialize(items)); } if (sqlconn.State == ConnectionState.Open) { sqlcmd.Dispose(); sqlconn.Close(); sqlconn.Dispose(); } } catch (Exception) { throw; } finally { if (sqlconn.State == ConnectionState.Open) { sqlcmd.Dispose(); sqlconn.Close(); sqlconn.Close(); } } } }
public Medicine Find(string name) { string query = "SELECT * FROM tbl_medicine WHERE name='" + name + "'"; ASqlCommand = new SqlCommand(query, ASqlConnection); ASqlConnection.Open(); ASqlDataReader = ASqlCommand.ExecuteReader(); if (ASqlDataReader.HasRows) { Medicine aMedicine = new Medicine(); ASqlDataReader.Read(); aMedicine.Id = (int)ASqlDataReader["id"]; aMedicine.Name = ASqlDataReader["name"].ToString(); aMedicine.Power = Convert.ToDecimal(ASqlDataReader["power"]); aMedicine.Type = ASqlDataReader["type"].ToString(); ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return aMedicine; } else { ASqlDataReader.Close(); ASqlCommand.Dispose(); ASqlConnection.Close(); return null; } }
private void button1_Click(object sender, EventArgs e) { int Row = dataGridView1.CurrentRow.Index; String ID = dataGridView1[0, Row].Value.ToString(); int affectedRows = 0; string connetionString = null; connetionString = "Data Source=MD\\SQLEXPRESS;Initial Catalog=lab;Integrated Security=True"; // connetionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString; SqlConnection con = new SqlConnection(connetionString); SqlCommand cmd = new SqlCommand(); //string myQuery = "INSERT INTO dbo.Employees (Name, DoB, Gender, Address, AddedBy, AddedDate) VALUES(@Name, @DoB, @Gender, @Address, @AddedBy, @AddedDate)"; string myQuery = "dbo.deleteEmployee"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = myQuery; cmd.Parameters.Add("@id", SqlDbType.NChar); cmd.Parameters["@id"].Value = ID; SqlParameter sqlP = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int); sqlP.Direction = ParameterDirection.ReturnValue; try { cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); affectedRows = (int)cmd.Parameters["@ReturnValue"].Value; if (affectedRows == 0) { MessageBox.Show("Амжилттай устгалаа "); cmd.Dispose(); con.Close(); } else if (affectedRows != 0) { MessageBox.Show("Хадгалагдсангүй", "Алдаа"); } else { cmd.Dispose(); con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message); cmd.Dispose(); con.Close(); } conn(); }
protected void Page_Load(object sender, EventArgs e) { string venid = Request.QueryString["id"]; // int vid = Convert.ToInt32(venid); /* itemfind.find s1 = new itemfind.find(); ArrayList a = new ArrayList(s1.item_retrieve(vid));*/ ArrayList c = new ArrayList(); ArrayList b = new ArrayList(); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbString"].ConnectionString); //Open the connection conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * from item_menus where item_id = " + venid + "", conn); SqlDataReader reader = cmd.ExecuteReader(); string a = "no rows"; while (reader.Read()) { a = reader["item_name"].ToString(); a = a + '^'; a = a + reader["item_price"].ToString(); a = a + '^'; a = a + reader["vendor_name"].ToString(); b.Add(a); } cmd.Dispose(); conn.Close(); if (Session["menulist"] != null) { c = (ArrayList)(Session["menulist"]); } foreach (string ss in b) { c.Add(ss); } Session["menulist"] = c; if (!IsPostBack) { string prevPage = Request.UrlReferrer.ToString(); Response.Redirect(prevPage); } cmd.Dispose(); conn.Close(); }
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 JsonResult get_DanhSachDonViTheoBangLuong(String iID_MaBangLuong ,String GiaTri) { List<Object> list = new List<Object>(); String SQL = "SELECT sDanhSachMaDonVi FROM L_BangLuong WHERE iTrangThai=1 AND iID_MaBangLuong=@iID_MaBangLuong"; SqlCommand cmd = new SqlCommand(SQL); cmd.Parameters.AddWithValue("@iID_MaBangLuong", iID_MaBangLuong); String dsMaDonVi = Connection.GetValueString(cmd, ""); cmd.Dispose(); String[] arrMaDonVi = dsMaDonVi.Split(','); cmd = new SqlCommand(); String DK = ""; int i; for (i = 0; i < arrMaDonVi.Length; i++) { if (arrMaDonVi[i] != "") { if (DK != "") DK += " OR "; DK += "iID_MaDonVi=@iID_MaDonVi" + i; cmd.Parameters.AddWithValue("@iID_MaDonVi" + i, arrMaDonVi[i]); } } DK += " AND iNamLamViec_DonVi=@iNamLamViec"; cmd.Parameters.AddWithValue("@iNamLamViec", NguoiDungCauHinhModels.iNamLamViec); SQL = String.Format("SELECT iID_MaDonVi, sTen FROM NS_DonVi WHERE iTrangThai=1 AND ({0}) AND (iID_MaDonVi + ' - ' + sTen) LIKE @iID_MaDonVi ORDER BY iID_MaDonVi", DK); cmd.CommandText = SQL; cmd.Parameters.AddWithValue("@iID_MaDonVi", GiaTri + "%"); DataTable dt = Connection.GetDataTable(cmd); cmd.Dispose(); if (dt.Rows.Count == 0) { dt.Dispose(); SQL = String.Format("SELECT iID_MaDonVi, sTen FROM NS_DonVi WHERE iTrangThai=1 AND ({0}) ORDER BY iID_MaDonVi", DK); cmd.CommandText = SQL; dt = Connection.GetDataTable(cmd); cmd.Dispose(); } for (i = 0; i < dt.Rows.Count; i++) { Object item = new { value = String.Format("{0}", dt.Rows[i]["iID_MaDonVi"]), label = String.Format("{0} - {1}", dt.Rows[i]["iID_MaDonVi"], dt.Rows[i]["sTen"]) }; list.Add(item); } dt.Dispose(); return Json(list, JsonRequestBehavior.AllowGet); }
public bool CheckIsRecordExist(string a_strChkSqlCommand) { System.Data.SqlClient.SqlCommand objCommand; bool RecordExists = false; try { bool AttemptToConnect = OpenSqlConnection(); objCommand = new System.Data.SqlClient.SqlCommand(a_strChkSqlCommand); objCommand.Connection = myConn; //objCommand.CommandTimeout=ConnectionTimeOut; objCommand.CommandType = CommandType.Text; string result = Convert.ToString(objCommand.ExecuteScalar()); objCommand.Dispose(); if (result != "") { RecordExists = true; } return(RecordExists); } catch (SqlException ex) { ex.Data.Clear(); return(RecordExists); } catch (Exception ex) { ex.Data.Clear(); return(RecordExists); } finally { CloseConnection(); } }
public string ExecuteQuery(string a_strSqlCommand) //Insert and update { if (OpenSqlConnection()) { CloseConnection(); OpenSqlConnection(); } System.Data.SqlClient.SqlCommand objCommand = new System.Data.SqlClient.SqlCommand(a_strSqlCommand); try { objCommand.Connection = myConn; objCommand.CommandType = CommandType.Text; objCommand.ExecuteNonQuery(); objCommand.Dispose(); return("Executed successfully"); } catch (Exception ex) { return(ex.Message.Trim()); } finally { CloseConnection(); } }
public string[] yread(string query, string[] columns) /*For Select*/ { string[] y = 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 { if (i > columns.Length) { break; } y[i] = reader[0].ToString().Trim(); i++; } catch { y[i] = "N/A"; } } reader.Close(); cmd.Dispose(); cn.Close(); cn.Dispose(); return(y); }
//新增參數式SQL的執行方法 public int ExecNonQuerySQLCommand(System.Data.SqlClient.SqlCommand SqlComUpdate, ref int AffectedRowCount) { AffectedRowCount = 0; if (CheckSession() == 1) { try { AffectedRowCount = SqlComUpdate.ExecuteNonQuery(); nRet = 0; } catch (Exception ex) { nRet = -1; outMsg = ex.Message; } finally { SqlComUpdate.Dispose(); SqlComUpdate = null; } } else { nRet = -1; outMsg = "SQL Connection is Disconnect."; } return(nRet); }
public int ExecuteSql(string SQLString, string content) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(SQLString, sqlConnection); System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NText); sqlParameter.Value = content; sqlCommand.Parameters.Add(sqlParameter); try { sqlConnection.Open(); int num = sqlCommand.ExecuteNonQuery(); result = num; } catch (System.Data.SqlClient.SqlException ex) { throw ex; } finally { sqlCommand.Dispose(); sqlConnection.Close(); } } return(result); }
public int ExecuteSqlInsertImg(string strSQL, byte[] fs) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(strSQL, sqlConnection); System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@fs", System.Data.SqlDbType.Image); sqlParameter.Value = fs; sqlCommand.Parameters.Add(sqlParameter); try { sqlConnection.Open(); int num = sqlCommand.ExecuteNonQuery(); result = num; } catch (System.Data.SqlClient.SqlException ex) { throw ex; } finally { sqlCommand.Dispose(); sqlConnection.Close(); } } return(result); }
//Grabado del detalle del arqueo private void guardaDetalleArqueo(int Celula, int AñoPed, int Pedido, double Saldo) { SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandText = "spCyCGuardaArqueoCobranzaPedido"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = _connection; cmd.Transaction = tr; cmd.Parameters.Add("@Arqueo", SqlDbType.Int).Value = _arqueo; cmd.Parameters.Add("@Celula", SqlDbType.TinyInt).Value = Celula; cmd.Parameters.Add("@AñoPed", SqlDbType.SmallInt).Value = AñoPed; cmd.Parameters.Add("@Pedido", SqlDbType.Int).Value = Pedido; cmd.Parameters.Add("@Saldo", SqlDbType.Money).Value = Saldo; try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); } }
protected void editOrderButton_Click(object sender, EventArgs e) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_UPS"].ConnectionString)) { int loopCounter = 0; System.Data.SqlClient.SqlDataReader reader = null; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //check for duplicates String query = "SELECT * FROM [ROReport_ProductArrival]"; cmd.CommandTimeout = 300; cmd.CommandText = query; cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = conn; conn.Open(); //cmd.Parameters.AddWithValue("@Order_Number", messageLabel1.Text); Console.Write("Executing Reader"); reader = cmd.ExecuteReader(); while (reader.Read()) { loopCounter++; } reader.Close(); cmd.Parameters.Clear(); cmd.Cancel(); cmd.Dispose(); conn.Close(); } }
public DataSet getDataSet(SqlCommand cmd) { DataSet dsRet = new DataSet(); try { cmd.Connection = getConnect(); cmd.CommandType = CommandType.StoredProcedure; using (DataSet ds = new DataSet()) { using (SqlDataAdapter da = new SqlDataAdapter()) { da.SelectCommand = cmd; da.Fill(ds); dsRet = ds; } } } catch (Exception ex) { throw new Exception(ex.ToString()); } finally // chay bt hoac chay co loi cung chay vao finnaly nay de dong ket noi va giai phong bo nho { if ((cmd.Connection != null) && (cmd.Connection.State == ConnectionState.Open)) cmd.Connection.Close(); //dong ket noi cmd.Dispose(); // } return dsRet; }
/// <summary> /// Lấy danh sách doanh nghiệp /// </summary> /// <param name="Quy">Quý</param> /// <param name="Nam">Năm</param> /// <param name="All">Lấy doanh nghiệp theo năm hay theo quý và năm</param> /// <returns></returns> public static DataTable GetDoanhNghiep(String Quy,String Nam) { String DKQuy = ""; if (Quy.Equals("5") || Quy.Equals("6")) DKQuy = "AND TCDN.iQuy=@iQuy-3"; else if (Quy.Equals("7")) DKQuy = ""; else DKQuy = "AND TCDN.iQuy=@iQuy"; DataTable dtDN = new DataTable(); String SQL = String.Format(@"SELECT TC.iID_MaDoanhNghiep,TC.sTenDoanhNghiep FROM TCDN_DoanhNghiep AS TC WHERE TC.iTrangThai=1 AND TC.iID_MaDoanhNghiep IN( SELECT TCDN.iID_MaDoanhNghiep FROM TCDN_KinhDoanh_ChungTuChiTiet AS TCDN WHERE TCDN.iTrangThai=1 AND TCDN.iNamLamViec=@iNamLamViec {0} )", DKQuy); SqlCommand cmd = new SqlCommand(SQL); cmd.Parameters.AddWithValue("@iNamLamViec", Nam); if (!String.IsNullOrEmpty(DKQuy)) { cmd.Parameters.AddWithValue("@iQuy", Quy); } dtDN = Connection.GetDataTable(cmd); cmd.Dispose(); return dtDN; }
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); }
// Protected implementation of Dispose pattern. protected virtual void Dispose(bool disposing) { if (disposed) { return; } if (disposing) { // Free any other managed objects here. objdb.Dispose(); dstCommon.Dispose(); adpCommon.Dispose(); cmd.Dispose(); objReturnObject.Dispose(); } // Free any unmanaged objects here. //objdb.Dispose(); //dstCommon.Dispose(); //adpCommon.Dispose(); //cmd.Dispose(); //objReturnObject.Dispose(); disposed = true; }
protected DataTable ExecuteDataTable(string procName, SqlParameter[] parameters) { SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); try { cmd = new System.Data.SqlClient.SqlCommand(procName, _connection); if (parameters != null) { cmd.Parameters.AddRange(parameters); } cmd.CommandType = CommandType.StoredProcedure; da.SelectCommand = cmd; da.Fill(dt); } catch (Exception x) { dt.Rows.Add(9, x.Message); } finally { cmd.Dispose(); _connection.Close(); } return(dt); }
public List <Settings> GetAll() { System.Data.SqlClient.SqlCommand cmd = null; System.Data.SqlClient.SqlDataReader SDR = null; System.Collections.Generic.List <Settings> ListTmp = new System.Collections.Generic.List <Settings>(); Settings ObjTmp = null; DBCon Obj = new DBCon(); try { string Query = "SELECT * FROM Settings "; cmd = new System.Data.SqlClient.SqlCommand(Query, Obj.Con); SDR = cmd.ExecuteReader(); while (SDR.Read()) { ObjTmp = new Settings { SettingId = SDR.GetInt32(0), KeyName = SDR.GetString(1), KeyValue = SDR.GetString(2), KeyDiscription = SDR.GetString(3), }; ListTmp.Add(ObjTmp); } } catch (System.Exception e) { e.ToString(); } finally { cmd.Dispose(); SDR.Close(); Obj.Con.Close(); Obj.Con.Dispose(); Obj.Con = null; } return(ListTmp); }
internal DataTable DataQuerying(string CmdString, string TableName) { SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(TableName); cmd.CommandText = CmdString; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = _connection; da.SelectCommand = cmd; try { OpenConnection(); da.Fill(dt); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { CloseConnection(); da.Dispose(); cmd.Dispose(); } return(dt); }
//Llamada al procedimiento de cancelación de arqueos private int cancelaArqueo() { SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandText = "spCyCCancelaArqueoCobranza"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = _connection; cmd.Transaction = tr; cmd.Parameters.Add("@Arqueo", SqlDbType.Int).Value = _arqueo; int rowsAffected = 0; try { rowsAffected = cmd.ExecuteNonQuery(); _arqueo = Convert.ToInt32(cmd.Parameters["@Arqueo"].Value); } catch (SqlException ex) { rowsAffected = -1; throw ex; } catch (Exception ex) { rowsAffected = -1; throw ex; } finally { cmd.Dispose(); } return(rowsAffected); }
public void Delete(OrganizationalUnitEntity t) { SqlConnection conn = null; SqlCommand cmd = null; try { conn = DALHelper.CreateSqlDbConnection(); cmd = new SqlCommand("usp_DeleteOrganizationalUnit", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Id", t.Id); cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); cmd.Dispose(); conn.Dispose(); } }
public void ConsultaListaCobranza() { SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); cmd.CommandText = "spCyCArqueoConsultaCobranza"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = _connection; da.SelectCommand = cmd; try { OpenConnection(); da.Fill(dsArqueo.Tables["PedidoCobranza"]); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { CloseConnection(); da.Dispose(); cmd.Dispose(); } }
/// <summary> /// return identity value /// </summary> /// <param name="sql"></param> /// <returns></returns> public long DoInsertReturnID(DataCollections _DataCol) { String sSQL; object _ReturnObject = null; sSQL = ToSQLString(DatabaseDAO.QueryTypes.Insert, _DataCol); sSQL += ";SELECT @@IDENTITY AS 'Identity'"; if (ConnectValid() == true) { try { System.Data.SqlClient.SqlCommand _SqlCommand = new System.Data.SqlClient.SqlCommand(sSQL, Sqlcon); if (hasTransaction == true) { _SqlCommand.Transaction = objTransaction; } _ReturnObject = _SqlCommand.ExecuteScalar(); _SqlCommand.Dispose(); _SqlCommand = null; if (hasTransaction == false) { Sqlcon.Close(); } return(long.Parse(_ReturnObject.ToString())); } catch (Exception obj) { throw new Exception(obj.Message); } } else { return(0); } }
public object ExecuteSqlGet(string SQLString, string content) { object result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(SQLString, sqlConnection); System.Data.SqlClient.SqlParameter sqlParameter = new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NText); sqlParameter.Value = content; sqlCommand.Parameters.Add(sqlParameter); try { sqlConnection.Open(); object obj = sqlCommand.ExecuteScalar(); if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value)) { result = null; } else { result = obj; } } catch (System.Data.SqlClient.SqlException ex) { throw ex; } finally { sqlCommand.Dispose(); sqlConnection.Close(); } } return(result); }
} //{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); }
private void btnReadDBs_Click(object sender, EventArgs e) { lbDatabases.Items.Clear(); try { string Connect = GenerateConnectionString(); SqlConnection con = new SqlConnection(Connect); con.Open(); SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_databases"; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { lbDatabases.Items.Add((dr.GetString(0))); } dr.Dispose(); cmd.Dispose(); con.Close(); con.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
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 IEnumerable<TipoUsuario> ObterTodos() { //Cria e abre a conexão com o banco de dados (essa string só serve para acessar o banco localmente) //Ver mais strings de conexão em http://www.connectionstrings.com/ SqlConnection conn = new SqlConnection("Server=SERVIDOR\\INSTANCIA;Integrated Security=true;"); conn.Open(); SqlCommand cmd; SqlDataReader reader; List<TipoUsuario> lista = new List<TipoUsuario>(); //Cria um comando para selecionar registros da tabela, trazendo todas as //pessoas que nasceram depois de 1/1/1900 cmd = new SqlCommand("SELECT ID_TIPO, DESCRICAO FROM TB_TIPO_USUARIO", conn); reader = cmd.ExecuteReader(); //Obtém os registros, um por vez while (reader.Read()) { TipoUsuario t = new TipoUsuario(); t.id_tipo = reader.GetInt32(0); t.descricao = reader.GetString(1); lista.Add(t); } reader.Close(); reader.Dispose(); cmd.Dispose(); //Fecha a conexão ao final pois ela não é mais necessária conn.Close(); conn.Dispose(); return lista; }
private void loadCombo() { SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ToString()); if ((Conn.State == ConnectionState.Closed)) { Conn.Open(); } System.Data.SqlClient.SqlCommand cmbreadOfficeLocations = new System.Data.SqlClient.SqlCommand("SELECT LocationID, CAST(LocationID AS nvarchar(5)) + ' - ' + LocationDes AS LocationDes FROM RefLocation WHERE (BlockLocation = 0)", Conn); SqlDataReader cmbreader; SqlCommand cmbUserCategory1 = new SqlCommand("SELECT UserCategoryCode, UserCategory FROM UserCategory where Active = 1", Conn); SqlDataReader cmbUserCategoryReader; cmbOffice.Items.Clear(); cmbUserCategory.Items.Clear(); cmbreader = cmbreadOfficeLocations.ExecuteReader(); cmbOffice.DataMember = "RefLocation"; cmbOffice.DataSource = cmbreader; cmbOffice.DataValueField = "LocationID"; cmbOffice.DataTextField = "LocationDes"; cmbOffice.DataBind(); cmbreader.Close(); cmbUserCategoryReader = cmbUserCategory1.ExecuteReader(); cmbUserCategory.DataMember = "UserCategory"; cmbUserCategory.DataSource = cmbUserCategoryReader; cmbUserCategory.DataValueField = "UserCategoryCode"; cmbUserCategory.DataTextField = "UserCategory"; cmbUserCategory.DataBind(); cmbUserCategory.Dispose(); cmbreadOfficeLocations.Dispose(); cmbUserCategoryReader.Close(); Conn.Close(); }
public Boolean ExcuteNonquery(SqlCommand cmd) { Boolean bRet = false; try { cmd.Connection = getConnect(); //cmd.CommandText = ""; cmd.CommandType = CommandType.StoredProcedure; // if (cmd.ExecuteNonQuery() > 0) //kiem tra so luong ban ghi tac dung bRet = true; else bRet = false; } catch (Exception ex) { bRet = false; throw new Exception(ex.ToString()); } finally // chay bt hoac chay co loi cung chay vao finnaly nay de dong ket noi va giai phong bo nho { if ((cmd.Connection != null) && (cmd.Connection.State == ConnectionState.Open)) cmd.Connection.Close(); //dong ket noi cmd.Dispose(); // } return bRet; }
private void button1_Click(object sender, EventArgs e) { try { if (con.State != ConnectionState.Open) con.Open(); for (int i = 0; i < dataGridView2.Rows.Count; i++) { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "UPDATE GRADOS SET NOMBRE= '" + Grados.Text + "' WHERE ID_GRADO = '" + dataGridView2.Rows[i].Cells[0].Value.ToString() + "'"; cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ene) { MessageBox.Show(ene.ToString()); } finally { if (con.State != ConnectionState.Closed) con.Close(); } this.Close(); }
// 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(); } }
public List <string> GetDBs() { List <string> items = new List <string>(); try { if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } sqlConn.Open(); SqlCommand sqlCom = new System.Data.SqlClient.SqlCommand(); sqlCom.Connection = sqlConn; sqlCom.CommandType = CommandType.StoredProcedure; sqlCom.CommandText = "sp_databases"; SqlDataReader sqlDR; sqlDR = sqlCom.ExecuteReader(); while (sqlDR.Read()) { items.Add(sqlDR.GetString(0)); } sqlCom.Dispose(); } catch (Exception ex) { } return(items); }
public bool tblPackageTime_Delete(int packageTimeId) { int i = 0; try { if (ConnectionData._MyConnection.State == ConnectionState.Closed) { ConnectionData._MyConnection.Open(); } SqlCommand cmd = new SqlCommand("DELETE FROM tblpackageTime WHERE packageTimeId = @packageTimeId", ConnectionData._MyConnection); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = packageTimeId; i = cmd.ExecuteNonQuery(); cmd.Dispose(); } catch (Exception ex) { } finally { ConnectionData._MyConnection.Close(); } return i > 0; }
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); }
/// <summary> /// Danh sách đơn vị có dữ liệu /// </summary> /// <param name="NamBangLuong">Năm bảng lương</param> /// <param name="ThangBangLuong">Tháng bảng lương</param> /// <param name="iID_MaTrangThaiDuyet">Mã trạng thái duyệt</param> /// <returns></returns> public static DataTable DanhSach_DonVi(String NamBangLuong, String ThangBangLuong, String iID_MaTrangThaiDuyet) { String DKTrangThaiDuyet = ""; if (iID_MaTrangThaiDuyet == "2") { DKTrangThaiDuyet = " AND iID_MaTrangThaiDuyet=@iID_MaTrangThaiDuyet"; } if (iID_MaTrangThaiDuyet == "-100") { DKTrangThaiDuyet = " AND iID_MaTrangThaiDuyet=-100"; } String SQLDonVi = String.Format(@"SELECT DISTINCT iID_MaDonVi,sTenDonVi,sTenDonVi as TenHT FROM l_BangLuongChiTiet WHERE iNamBangLuong=@NamBangLuong AND iThangBangLuong=@ThangBangLuong AND iTrangThai=1 {0}", DKTrangThaiDuyet); SqlCommand cmd = new SqlCommand(SQLDonVi); cmd.Parameters.AddWithValue("@NamBangLuong", NamBangLuong); cmd.Parameters.AddWithValue("ThangBangLuong", ThangBangLuong); if (iID_MaTrangThaiDuyet != "-1") { cmd.Parameters.AddWithValue("@iID_MaTrangThaiDuyet", LuongCongViecModel.Get_iID_MaTrangThaiDuyet_DaDuyet(PhanHeModels.iID_MaPhanHeLuong)); } DataTable dtDonVi = Connection.GetDataTable(cmd); cmd.Dispose(); DataRow r = dtDonVi.NewRow(); r[2] = "-- Chọn đơn vị --"; dtDonVi.Rows.InsertAt(r, 0); // dtDonVi.Dispose(); return dtDonVi; }
protected void populateCompList() { using (var con = new System.Data.SqlClient.SqlConnection(conString)) { using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM tb_Company", con)) { try { con.Open(); } catch (Exception e) { //lblErrorMsg.Text = e.Message; } var reader = cmd.ExecuteReader(); ftCompany.DataSource = reader; ftCompany.DataValueField = "companyName"; ftCompany.DataTextField = "companyName"; ftCompany.DataBind(); cmd.Dispose(); reader.Close(); } } }
}// fin FS_CLI_05_CPA_GET_PLANT_CENTER_ID /// <summary> /// Metdodo que devuelve los datos de un usuario /// </summary> /// <param name="USUARIO"></param> /// <returns></returns> public DataSet SP_04_GET_DATOS_USUARIO(string USUARIO) { Conectar(); DataSet result = new DataSet(); try { System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("SP_04_GET_DATOS_USUARIO", cnn); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandTimeout = _commandTimeout; command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@USUARIO", System.Data.SqlDbType.NVarChar)); command.Parameters["@USUARIO"].Value = USUARIO; System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command); da.Fill(result); command.Dispose(); } catch (Exception e) { result = null; Console.WriteLine(e.StackTrace); } finally { Desconectar(); } return(result); }// fin SP_04_GET_DATOS_USUARIO
protected void Button2_Click(object sender, EventArgs e) { //Rentalid = new List<string>(); string strConnectionString = "Addr =192.168.10.172;User Id=sa;password=P@ssw0rd;Initial Catalog=DVDRentalDB_B;Integrated Security =false"; SqlConnection hConnection = (new SqlConnection(strConnectionString)); hConnection.Open(); System.Data.SqlClient.SqlCommand hCommand = hConnection.CreateCommand(); hCommand.CommandText = "SELECT [dbo].[DVDMaster].[Name],[dbo].[Member].[LoginName],[dbo].[Rental].[Id] FROM [dbo].[Rental] INNER JOIN [dbo].[DVDMaster] ON [dbo].[DVDMaster].[id] = [dbo].[Rental].[DvdId] INNER JOIN [dbo].[Member] ON [dbo].[Rental].[UserId] = [dbo].[Member].[Id]"; System.Data.SqlClient.SqlDataReader hReader = hCommand.ExecuteReader(); hCommand.Dispose(); while (hReader.Read()) { if (hReader["LoginName"].ToString() == TextBox1.Text) { cbRentalId.Items.Add(hReader["Id"].ToString()); CheckBoxList1.Items.Add(hReader["Name"].ToString()); } } if (CheckBoxList1.Items.Count == 0) { _Labl3.Text = "レンタルされている商品はありません"; } hReader.Close(); hConnection.Close(); hConnection.Dispose(); }