C# SqlDataReader.Close方法代码示例

 protected void btnSignIn_Click(object sender, ImageClickEventArgs e)
 {
     if (txtUsername.Text.Trim() == "" || txtPassword.Text.Trim() == "")
         lblMessage.Text = "Both fields are required";
     else
     {
         query = "select usertype from Login where username='" + txtUsername.Text + "' AND Password='" + txtPassword.Text + "'";
         reader = db.data_read(query);
         if (reader.Read())
         {
             if (reader.GetString(0).Trim() == "Employee") // Go to employee's home page
             {
                 Session["username"] = txtUsername.Text;
                 reader.Close();
                 Response.Redirect("~/employee/EmployeeHome.aspx");
             }
             else if (reader.GetString(0).Trim() == "Employer")// Go to employer's home page
             {
                 Session["username"] = txtUsername.Text;
                 reader.Close();
                 Response.Redirect("~/employer/EmployerHome.aspx");
             }
             else // Go to admin's home page
             {
                 Session["username"] = txtUsername.Text;
                 Response.Redirect("~/admin/Home.aspx");
             }
         }
         else
         {
             lblMessage.Text = "Invalid Username/Password or no such user exists";
         }
     }
 }

        public User getUser(string _username, string _pass)
        {
            User user = null;

            try
            {
                conn = db.openConn();
                sb = new StringBuilder();

                sb.Remove(0, sb.Length);
                sb.Append(" SELECT ID,UserName,Password,Ulevel FROM tbUser ");
                sb.Append(" WHERE (UserName='" + _username + "')");
                sb.Append(" AND (Password='" + _pass + "')");

                string sql;
                sql = sb.ToString();

                comm = new SqlCommand();
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;
                comm.Connection = conn;
                dr = comm.ExecuteReader();
                if (dr.HasRows)
                {

                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    int index = 1;
                    foreach (DataRow drw in dt.Rows)
                    {
                        user = new User();
                        user.UID = drw["ID"].ToString();
                        user.UserName = drw["UserName"].ToString();
                        user.Password = drw["Password"].ToString();
                        user.level = drw["Ulevel"].ToString();

                    }

                }

                dr.Close();

            }
            catch (Exception ex)
            {
                dr.Close();
                conn.Close();
                return null;
                throw ex;

            }
            finally
            {
                conn.Close();
            }

            return user;
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();

                com.Connection = conn;
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@UserName", tbxUserName.Text);
                com.Parameters.AddWithValue("@Email", tbxEmail.Text);
                com.CommandText = "CheckUsers";

                //string checkUsers = "SELECT UserName from UserTbl where UserName = @UserName OR Email = @Email";
                //SqlCommand checkCom = new SqlCommand(checkUsers, conn);
                //checkCom.Parameters.AddWithValue("@UserName", tbxUserName.Text);
                //checkCom.Parameters.AddWithValue("@Email", tbxEmail.Text);
                reader = com.ExecuteReader();

                if (reader.Read())
                {
                    lblError.Text = "User name or Email is unavailable";
                    reader.Close();
                }
                else
                {
                    reader.Close();
                    com.Connection = conn;

                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = "RegisterUser";
                    com.Parameters.Clear();
                    com.Parameters.AddWithValue("@UserName", tbxUserName.Text);
                    com.Parameters.AddWithValue("@Email", tbxEmail.Text);
                    com.Parameters.AddWithValue("@Password", Utilities.GetMD5Hash(tbxPassword.Text));
                    com.Parameters.AddWithValue("@DOB", tbxDOB.Text);
                    com.Parameters.AddWithValue("@ContactNo", tbxContactNum.Text);

                    com.ExecuteNonQuery();
                    reader = com.ExecuteReader();

                    Response.Redirect("LogIn.aspx");
                }
            }
            catch(Exception ex)
            {
                Response.Write("Error: " + ex.ToString());
            }
            finally
            {
                reader.Close();
                conn.Close();
            }
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Technetium.mdf;Integrated Security=True;User Instance=True");
        if (!IsPostBack)
        {
            if (Session["uid"] == null)
            {
                Response.Redirect("Default.aspx");
                return;
            }
            try
            {
                int uid = int.Parse(Session["uid"].ToString());
                cn.Open();
                cmd = new SqlCommand("select * from Registration_Master where User_Id=" + uid, cn);
                dr = cmd.ExecuteReader();
                dr.Read();
                imgprofile_pic.ImageUrl = dr["User_Photo"].ToString();
                btnchoose.Enabled = false;
                dr.Close();
                cn.Close();
            }
            catch
            {
            }
        }


    }

    protected void Btnpreview_Click(object sender, EventArgs e)
    {
        imgprofile_pic.Visible = true;
        int uid = int.Parse(Session["uid"].ToString());
        if (fluplod.HasFile == true)
        {
            cn.Open();
            cmd = new SqlCommand("Select Email_id from Registration_Master Where User_Id=" + uid,cn);
            dr = cmd.ExecuteReader();
            dr.Read();
            string email = dr["Email_Id"].ToString();
            dr.Close();
            if(!Directory.Exists(Server.MapPath("~/User/"+email+"/Images")))
            {
            Directory.CreateDirectory(Server.MapPath("~/Users/"+email+"/Profilepic"));
            }
            string fpath = "~/Users/"+email+"/Profilepic/" + fluplod.FileName;
            fluplod.PostedFile.SaveAs(Server.MapPath(fpath));
            cmd = new SqlCommand("Update Registration_Master set [email protected]_photo where User_Id=" + uid, cn);
            cmd.Parameters.AddWithValue("user_photo", fpath);
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //int uid = int.Parse(Session["uid"].ToString());
        cn.Open();
        cmd = new SqlCommand("select * from Registration_Master where User_Id=" + uid, cn);
        dr = cmd.ExecuteReader();
        dr.Read();
        imgprofile_pic.ImageUrl = dr["User_Photo"].ToString();
        fluplod.Enabled = false;
        btnchoose.Enabled = true;
        cn.Close();


    }

        public void actualizador()
        {
            conexion.ConnectionString = cadena_conexion;
            conexion.Open();
            Consulta = "select * from plantas";
            comando.Connection = conexion;
            comando.CommandText = Consulta;
            Lector = comando.ExecuteReader();
            int cantidades_de_registros = 0;
            Datos = new Plantas[30];
            while (Lector.Read() == true)
            {
                Herbarios = new Plantas();
                Herbarios.Codigo = Convert.ToInt32(Lector["COD_PLANTA"]);
                Herbarios.Descripcion = Convert.ToString(Lector["DESCRIPCION"]);
                Herbarios.CodigoPlanta = Convert.ToInt32(Lector["COD_TIPO_PLANTA"]);
                Herbarios.Precio = Convert.ToDouble(Lector["PRECIO"]);
                Herbarios.Stock = Convert.ToInt32(Lector["STOCK"]);
                Herbarios.Clima = Convert.ToString(Lector["clima"]);
                Datos[cantidades_de_registros] = Herbarios;
                lstMostrador.Items.Add(Datos[cantidades_de_registros].toString());
                cantidades_de_registros++;

            }
            conexion.Close();
                Lector.Close();
        }

 private void button1_Click(object sender, EventArgs e)
 {
     string kode = getOppAlias();
     // berikut ini adalah contoh penggunaan string.format. dimana {0} untuk parameter 1st dan {1} parameter 2nd
     //MessageBox.Show(string.Format("Menit: {0:MM} \nJam: {1}", DateTime.Now, DateTime.Now.ToShortTimeString()));
     try
     {
         koneksi.Open();
         reader = konek.MembacaData("SELECT COUNT(*) FROM KASDA..BLJ_MASTER WHERE YEAR(Tgl_SP) = YEAR(GETDATE())", koneksi);
         if (reader.HasRows)
         {
             reader.Read();
             if (alat.PengecekField(reader, 0) == "0")
             {
                 Clipboard.SetText(string.Format("1/{0:MM}/{1:yy}/{2}", DateTime.Now, DateTime.Now, kode));
                 //MessageBox.Show("Test");
             }
             else
                 //MessageBox.Show(string.Format("{0}/{1:MM}/{2:yy}/{3}", alat.pengecekField(reader, 0),
                 //    DateTime.Now, DateTime.Now, kode));
                 Clipboard.SetText(string.Format("{0}/{1:MM}/{2:yy}/{3}", alat.PengecekField(reader, 0),
                     DateTime.Now, DateTime.Now, kode));
             reader.Close();
         }
         koneksi.Close();
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }

        private void btnConvertExcel_Click(object sender, EventArgs e)
        {
            //Class.ExportToExcel.Export2Excel(dgvPrintSaleDayExcel);

            com = new SqlCommand();
            com.Parameters.Clear();
            com.Parameters.Add("@saleDate", SqlDbType.DateTime).Value = Convert.ToDateTime(saleDate);
            com.CommandText = strExcelToFormula;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;
            dr = com.ExecuteReader();
            DataTable dtFormula = new DataTable();
            dtFormula.Load(dr);
            dtFormula.Columns["ProductId"].ColumnName = "รหัสสินค้า";
            dtFormula.Columns["SaleHeadId"].ColumnName = "ใบยืม";
            dtFormula.Columns["SaleDetailQuantity"].ColumnName = "จำนวน";
            dtFormula.Columns["SaleDetailPriceUnit"].ColumnName = "ราคาขาย";
            dtFormula.Columns["Cash"].ColumnName = "ขายสด";
            ds = new DataSet();
            ds.Tables.Add(dtFormula);
            dr.Close();
            Class.ExportToExcel.Export2Excel(ds);

            frmCheckSaleDaily frm = new frmCheckSaleDaily();
            frm.Close();
        }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (refnum.Value == "")
        {
            MessageBox.Show("Please enter a reference number..");
            refnum.Focus();
        }
        else
        {
            try
            {
                cmd = new SqlCommand("select * from bookticketdb where refnum=" + refnum.Value, con);
                dr = cmd.ExecuteReader();
                dr.Read();
                mov = dr["movname"].ToString();
                seat = dr["seats"].ToString();
                date = dr["movdate"].ToString();
                time = dr["movtime"].ToString();
                bid = dr["bookid"].ToString();
                cost = Convert.ToInt32(dr["cost"].ToString());
                dr.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show("Followng Error occured :" + err.Message);

            }
        }
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        con.Open();
        //Session["s1"] = DropDownList1.SelectedItem.ToString();
        cmd = new SqlCommand("select * from add_passenger where invoice_no = '" + DropDownList1.SelectedItem.ToString() + "'" ,con);
        dr =  cmd.ExecuteReader();
        while(dr.Read())
        {
            a = dr["Invoice_No"].ToString();
            b = dr["Flight_no"].ToString();
            c = dr["Date_Of_Travel"].ToString();
            d = dr["Source"].ToString();
            f = dr["Destination"].ToString();
            g = dr["Category"].ToString();
            h= dr["Passenger_Name"].ToString();
            i = dr["Age"].ToString();
            j = dr["Gender"].ToString();
            k = dr["Phone_No"].ToString();
            l = dr["E_Mail"].ToString();
            m = dr["City"].ToString();

        }
        dr.Close();
        con.Close();
        con.Open();
        cmd = new SqlCommand("Insert into Seat_Cancellation Values('"+a+"','"+b+"','"+c+"','"+d+"','"+f+"','"+g+"','"+h+"','"+i+"','"+j+"','"+k+"','"+l+"','"+m+"')",con);
        cmd.ExecuteNonQuery();
        con.Close();
        con.Open();
        con.Close();
        Session["bb"] = DropDownList1.SelectedItem.ToString();
         Response.Redirect("Refund.aspx");
       }

        // 비밀번호 변경 - 입력한 기존 비밀번호가 일치?)
        public bool ChangePW_isMember(string id, string pw)
        {
            try
            {
                conn.Open();
                string sql_truncate = "SELECT * FROM member WHERE [email protected] AND [email protected]";
                command = new SqlCommand(sql_truncate, conn);
                command.Parameters.Add("@id", SqlDbType.VarChar);
                command.Parameters.Add("@pw", SqlDbType.VarChar);

                command.Parameters["@id"].Value = id;
                command.Parameters["@pw"].Value = pw;
                reader = command.ExecuteReader();
                return reader.HasRows;
            }

            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

            finally
            {
                reader.Close();
                conn.Close();
            }

            return reader.HasRows;
        }

        // When a DVD is selected, populate the textboxes with its information
        protected void btnSelect_Click(object sender, EventArgs e)
        {
            // Enable update and delete buttons
            btnDelete.Enabled = true;
            btnUpdate.Enabled = true;

            setConnection();
            myCmd = new SqlCommand("SELECT DVDtitle, DVDartist, DVDrating, DVDprice FROM DVDtable WHERE DVDID = " + dvdDropdown.SelectedValue, myConnection);

            try
            {
                myConnection.Open();
                myReader = myCmd.ExecuteReader();

                while (myReader.Read())
                {
                    txtTitle.Text = (myReader["DVDtitle"].ToString());
                    txtArtist.Text = (myReader["DVDartist"].ToString());
                    txtRating.Text = (myReader["DVDrating"].ToString());
                    txtPrice.Text = (myReader["DVDprice"].ToString());
                }

                myReader.Close();
            }
            finally
            {
                myConnection.Close();
            }
        }

        protected void Listar_Click(object sender, EventArgs e)
        {
            string nome;
            int idBusca;

            ListaNomes.Items.Clear();

            if(int.TryParse(textID.Text, out idBusca) == false)
            {
                lblMensagem.Text = "Campo de busca ID em branco..!";
                return;
            }

            conn.Open();
            command = new SqlCommand("SELECT Id, Nome FROM tbPessoa WHERE Id >= @a", conn);
            command.Parameters.AddWithValue("@a", idBusca);
            reader = command.ExecuteReader();

            while(reader.Read())
            {
                idBusca = reader.GetInt32(0);
                nome = reader.GetString(1);
                ListItem item = new ListItem(nome, Convert.ToString(idBusca));
                ListaNomes.Items.Add(item);
            }

            reader.Close();
            reader.Dispose();
            command.Dispose();
            conn.Close();
            conn.Dispose();
        }

        public int peliculaRegistrada(int CategoriaId)
        {
            int cont = 0;

            try
            {
                cmd = new SqlCommand(string.Format("select *from registroPelicula where categoriaId = {0}", CategoriaId), con);

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    cont++;
                }

                dr.Close();
            }

            catch (Exception ex)
            {

                MessageBox.Show("No hay Vida: "+ex.ToString ());
            }
            return cont;
        }

 /// <summary>
 /// ��DataReader תΪ DataTable
 /// </summary>
 /// <param name="DataReader">DataReader</param>
 public DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
 {
     DataTable datatable = new DataTable();
     try
     {
         DataTable schemaTable = dataReader.GetSchemaTable();
         //��̬�����
         foreach (DataRow myRow in schemaTable.Rows)
         {
             DataColumn myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.String");
             myDataColumn.ColumnName = myRow[0].ToString();
             datatable.Columns.Add(myDataColumn);
         }
         //�������
         while (dataReader.Read())
         {
             DataRow myDataRow = datatable.NewRow();
             for (int i = 0; i < schemaTable.Rows.Count; i++)
             {
                 myDataRow[i] = dataReader[i].ToString();
             }
             datatable.Rows.Add(myDataRow);
             myDataRow = null;
         }
         schemaTable = null;
     }
     finally
     {
         dataReader.Close();
         dataReader.Dispose();
     }
     return datatable;
 }