//Form 1
 public void dataGrid(DataTable dt, object table)
 {
     System.Data.SqlClient.SqlDataAdapter da;
     string sql = "SELECT * FROM " + table;
     da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
     da.Fill(dt);
 }

 private void ReloadStatistics()
 {
     System.Data.SqlClient.SqlCommand cmd = null;
     string d_member = "";
     string v_member = "";
     if(this.tsmiByReceiver.Checked){
         cmd = Statistics.Contents.ByReceiver(this.receipt, out d_member, out v_member);
     }
     else if (this.tsmiByBuyer.Checked)
     {
         cmd = Statistics.Contents.ByBuyer(this.receipt, out d_member, out v_member);
     }else if( this.tsmiByProductTypes.Checked){
         cmd = Statistics.Contents.ByProductTypes(this.receipt, out d_member, out v_member);
     }
     else if (this.tsmiByCategories.Checked)
     {
         cmd = Statistics.Contents.ByCategories(this.receipt, out d_member, out v_member);
     }
     if (cmd != null)
     {
         System.Data.DataTable content_stat = new DataTable("ContentsStatistics");
         cmd.Connection = this.connection;
         System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd);
         sda.Fill(content_stat);
         this.dgvData.DataSource = content_stat;
         this.dgvData.Columns[d_member].DisplayIndex = 0;
         this.dgvData.Columns[v_member].DisplayIndex = 1;
     }
     else
     {
         MessageBox.Show("Не выбрано ни одного критерия!", "Ошибка");
     }
     return;
 }

 //Form 1
 public void dataGrid2(DataTable dt, object textbox)
 {
     System.Data.SqlClient.SqlDataAdapter da;
     string sql = string.Format("SELECT * FROM itemtable where Item_Name LIKE '%{0}%'", textbox);
     da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
     da.Fill(dt);
 }

    private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario)
    {
      // Aca se lee la informacion de la base de datos
      // y se preparan los layers
      string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString();
      System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
      conn.Open();

      System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand
        ("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn);

      System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100);
      prm.Value = IDParametro;
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int);
      if (IDPortal.HasValue)
      {
        prm.Value = IDPortal.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int);
      if (IDSistema.HasValue)
      {
        prm.Value = IDSistema.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar);
      if (IDUsuario != null)
      {
        prm.Value = IDUsuario;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      //     IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora
      cmd.CommandType = System.Data.CommandType.Text;
      System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

      System.Data.DataSet ds = new System.Data.DataSet();
      da.Fill(ds);

      conn.Close();

      return ds.Tables[0].Rows[0];
      //return resultado;
    }

        public static System.Data.DataTable GetDataTable(string strSQL)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();

            csb.DataSource = System.Environment.MachineName;
            csb.DataSource = @"VMSTZHDB08\SZH_DBH_1";
            csb.InitialCatalog = "HBD_CAFM_V3";

            csb.DataSource = "CORDB2008R2";
            csb.InitialCatalog = "Roomplanning";

            // csb.DataSource = "cordb2014";
            // csb.InitialCatalog = "ReportServer";

            csb.DataSource = @"CORDB2008R2";
            csb.InitialCatalog = "COR_Basic_SwissLife";

            csb.IntegratedSecurity = true;

            using (System.Data.Common.DbDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSQL, csb.ConnectionString))
            {
                da.Fill(dt);
            }

            return dt;
        }

        private void ChangePositionForm_Load(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlDataAdapter sda = null;

            System.Data.SqlClient.SqlCommand prdccmd = Producer.Commands.Products(-1, -1 );
            prdccmd.Connection = this.connection;
            sda = new System.Data.SqlClient.SqlDataAdapter(prdccmd);
            this.products = new System.Data.DataTable("Products");
            sda.Fill(this.products);
            this.cbxProducts.DataSource = this.products;
            this.cbxProducts.DisplayMember = "ProductName";
            this.cbxProducts.ValueMember = "ProductID";
            this.cbxProducts.SelectedValue = this.product_id;

            prdccmd = Producer.Commands.Products( -1, this.product_id );
            prdccmd.Connection = this.connection;
            sda = new System.Data.SqlClient.SqlDataAdapter(prdccmd);
            System.Data.DataTable prods = new System.Data.DataTable("Product");
            sda.Fill(prods);

            System.Data.SqlClient.SqlCommand catccmd = Producer.Commands.ProductCategories();
            catccmd.Connection = this.connection;
            sda = new System.Data.SqlClient.SqlDataAdapter(catccmd);
            this.categories = new System.Data.DataTable("Categories");
            sda.Fill(this.categories);
            this.cbxCategory.DataSource = this.categories;
            this.cbxCategory.DisplayMember = "CategoryName";
            this.cbxCategory.ValueMember = "CategoryID";
            this.cbxCategory.SelectedValue = prods.Rows[0]["Category"];
            //this.block = false;

            //this.tbxCurrentProduct.Text = this.product["ProductName"].ToString();
        }

 private void cbxCategories_SelectedIndexChanged(object sender, EventArgs e)
 {
     if (!this.bBlockContent)
     {
         this.bBlockContent = true;
         System.Data.SqlClient.SqlCommand cmd = null;
         if (this.cbxCategories.SelectedIndex >= 0)
         {
             cmd = Producer.Commands.Products((Guid)this.cbxCategories.SelectedValue, System.Guid.Empty);
         }
         else
         {
             cmd = Producer.Commands.Products(Guid.Empty, System.Guid.Empty);
         }
         cmd.Connection = this.cConnection;
         System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd);
         this.products = new DataTable("Products");
         sda.Fill(this.products);
         this.cbxProducts.DataSource = this.products;
         this.cbxProducts.ValueMember = "ProductID";
         this.cbxProducts.DisplayMember = "ProductName";
         if( this.products.Rows.Count > 0 )
             this.LoadPrices((Guid)this.products.Rows[0]["ProductID"]);
         this.bBlockContent = false;
     }
 }

        private void ProductTypeForm_Load(object sender, EventArgs e)
        {
            // TODO сделать для обновления
            System.Data.SqlClient.SqlCommand cat_cmd = Producer.Categories.Select(Guid.Empty);
            cat_cmd.Connection = this.cConnection;
            System.Data.SqlClient.SqlDataAdapter catda = new System.Data.SqlClient.SqlDataAdapter(cat_cmd);
            System.Data.DataTable tbl = new System.Data.DataTable("Categories");
            catda.Fill(tbl);
            this.cbxCategories.DataSource = tbl;
            this.cbxCategories.DisplayMember = "CategoryName";
            this.cbxCategories.ValueMember = "CategoryID";
            string col_name = "Category";
            if (!System.Convert.IsDBNull(this.product_type[col_name])) this.cbxCategories.SelectedValue = this.product_type[col_name];
            this.isNewType = (System.Convert.IsDBNull(this.product_type["TypeId"]) || ((int)this.product_type["TypeId"]) < 0);

            string caption = "Добавление нового типа продукта";
            if (!this.isNewType)
            {
                int ptype = -1;
                col_name = "TypeId";
                if (!System.Convert.IsDBNull(this.product_type[col_name]))
                    ptype = (int)this.product_type[col_name];
                col_name = "Name";
                if (!System.Convert.IsDBNull(this.product_type[col_name]))
                    this.tbxProductType.Text = (string)this.product_type[col_name];
                col_name = "Comment";
                if (!System.Convert.IsDBNull(this.product_type[col_name]))
                    this.tbxComment.Text = (string)this.product_type[col_name];

                caption = string.Format("Редактирование типа продукта #{0}", ptype);
            }
            this.Text = caption;
            return;
        }

        private void FillDataSet()
        {

            //1. Make a Connection
            System.Data.SqlClient.SqlConnection objCon;
            objCon = new System.Data.SqlClient.SqlConnection();
            objCon.ConnectionString = @"Data Source=(localDB)\v11.0;Initial Catalog = EmployeeProjects; Integrated Security=True;";
            objCon.Open();

            //2. Issue a Command
            System.Data.SqlClient.SqlCommand objCmd;
            objCmd = new System.Data.SqlClient.SqlCommand();
            objCmd.Connection = objCon;
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = @"pSelEmployeeProjectHours";

            //3. Process the Results
            System.Data.DataSet objDS = new DataSet();
            System.Data.SqlClient.SqlDataAdapter objDA;
            objDA = new System.Data.SqlClient.SqlDataAdapter();
            objDA.SelectCommand = objCmd;
            objDA.Fill(objDS); // objCon.Open() is not needed!
            dataGridView1.DataSource = objDS.Tables[0];

            //4. Clean up code
            objCon.Close();
            dataGridView1.Refresh();
        }

        protected void cmdSearch_OnClick(object source, EventArgs e)
        {
            try
            {

                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
                conn.ConnectionString = "Server=devrystudentsp10.db.6077598.hostedresource.com;User Id=DeVryStudentSP10;Password=OidLZqBv4;";
                conn.Open();
                // Console.WriteLine(conn.State);
                System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
                comm.Connection = conn;

                string SQL = "select usertrackerid, trackkey, value,trackwhen from huber_tracker12 where 1=1";
                if (txtKey.Text.Length > 0)
                {
                    SQL += " and trackkey like @trackkey";
                    comm.Parameters.AddWithValue("@trackkey", "%" + txtKey.Text + "%");
                }
                if (txtValue.Text.Length > 0)
                {
                    SQL += " and value like @value ";
                    comm.Parameters.AddWithValue("@value", "%" + txtValue.Text + "%");
                }
                Response.Write(SQL);
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
                comm.CommandText = SQL;

                #region "Dataset"
                //this is a dataset
                // System.Data.DataSet ds = new System.Data.DataSet();
                //da.SelectCommand = comm;
                //da.Fill(ds);
                //this is a dataset
                //rptDisplay.DataSource = ds.Tables[0];
                #endregion

                System.Data.SqlClient.SqlDataReader dr;
                dr = comm.ExecuteReader();

                if (dr.HasRows)
                {
                    rptDisplay.DataSource = dr;
                    rptDisplay.DataBind();
                }
            }
            catch (System.Data.SqlClient.SqlException sqlex)
            {
                //throw new would go with the changes to your web.config
                //throw new Exception("Connecting to DB", sqlex.InnerException);

                //this way is "roll your own"
                Response.Redirect("anerrorpage.aspx?msg=Error Connecting To DB");
            }
            catch (Exception ex)
            {
                Response.Write("error!");

            }
        }

 public ReturnResult CheckUserAccount(string UserAccount, string UserPassWord)
 {
     ReturnResult ReturnResult = new ReturnResult();
     ConnStr(0);
     System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
     cmd.Connection = conn;
     cmd.CommandType = CommandType.Text;
     cmd.CommandTimeout = 300;
     System.Data.SqlClient.SqlDataAdapter Adp;
     cmd.CommandText = "Select Login_id,Check_result from SchManage.dbo.V_Sys_id where [email protected]_id and [email protected]"; //只要不是1就不是審核跟啟用
     cmd.Parameters.AddWithValue("@Login_id", UserAccount);
     cmd.Parameters.AddWithValue("@PWD",  GetMD5(UserPassWord.Trim().ToUpper())); 
     Adp = new System.Data.SqlClient.SqlDataAdapter(cmd);
     DataSet DBViewDS = new DataSet();
     try
     {
         Adp.Fill(DBViewDS); 
     }
     catch (Exception ex)
     {
         ReturnResult.ReturnMsgNo = -99;
         ReturnResult.ReturnMsg = "WS的取得CheckUserAccount資料失敗" + ex.Message;
         return ReturnResult;
     }
     if (DBViewDS.Tables.Count == 0)
     {
         ReturnResult.ReturnMsgNo = -98;
         ReturnResult.ReturnMsg = "查詢不到您的帳號";
         return ReturnResult;
     }
     if (DBViewDS.Tables[0].Rows.Count == 0)
     {
         ReturnResult.ReturnMsgNo = -98;
         ReturnResult.ReturnMsg = "查詢不到您的帳號";
         return ReturnResult;
     }
     if (Convert.ToString(DBViewDS.Tables[0].Rows[0]["Login_id"]).ToLower() == UserAccount.Trim().ToLower())
     {
         if (Convert.ToInt32(DBViewDS.Tables[0].Rows[0]["Check_result"]) == 1)
         {
             ReturnResult.ReturnMsgNo = 1;
             ReturnResult.ReturnMsg = "登入成功";
             return ReturnResult;
         }
         else
         {
             ReturnResult.ReturnMsgNo = -97;
             ReturnResult.ReturnMsg = "帳號尚未啟用";
             return ReturnResult;
         }
     }
     else
     {
         ReturnResult.ReturnMsgNo = -96;
         ReturnResult.ReturnMsg = "帳號不符";
         return ReturnResult;
     } 
     return ReturnResult;
 }

 public static System.Data.DataTable GetDataTable(string sql, System.Data.SqlClient.SqlConnection conn)
 {
     System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
     adp.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
     System.Data.DataSet ds = new System.Data.DataSet();
     adp.Fill(ds);
     System.Data.DataTable tbl = ds.Tables[0];
     return tbl;
 }

        /// <summary>
        /// 构造函数，接收一个SqlServer数据库连接对象SqlConnection
        /// </summary>
        public SqlDbOperHandler(System.Data.SqlClient.SqlConnection _conn)
        {
            conn = _conn;
            dbType = DatabaseType.SqlServer;

            conn.Open();
            cmd = conn.CreateCommand();
            da = new System.Data.SqlClient.SqlDataAdapter();
        }

 public System.Data.DataSet m_getRows(string rq_sql, string rows)
 {
     this.oDS = new DataSet();
     this.rq_sql = rq_sql;
     this.oCMD = new System.Data.SqlClient.SqlCommand(rq_sql, this.oCNX);
     this.oDA = new System.Data.SqlClient.SqlDataAdapter(this.oCMD);
     this.oDA.Fill(this.oDS, rows);
     return this.oDS;
 }

        static RoomClient()
        {
            
            //string ConnectString = "metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string="Server=192.168.2.24,8605;Database=dbroom;User ID=sa;Password=654321;MultipleActiveResultSets=True"";
            //System.Data.SqlClient.SqlConnectionStringBuilder sqlConnection = new System.Data.SqlClient.SqlConnectionStringBuilder();
            //sqlConnection.DataSource = @"192.168.2.24,8605";
            //sqlConnection.ApplicationName = "";
            //sqlConnection.InitialCatalog = "dbroom";
            //sqlConnection.IntegratedSecurity = true;
            //sqlConnection.PersistSecurityInfo = true;
            //sqlConnection.UserID = "sa";
            //sqlConnection.Password ="654321";

            //string connectString = "Server=10.21.99.82;Database=SecureDB;User ID=secure;Password=secure;";
            string connectString = "data source=10.21.99.80;initial catalog=SecureDB;persist security info=True;user id=secure;password=secure;MultipleActiveResultSets=True;App=EntityFramework;";
            string dir = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
            dir = dir.Remove(dir.LastIndexOf('\\'));
            if (System.IO.File.Exists(dir [email protected]"\Server.txt"))
            {
                string serverSet = System.IO.File.ReadLines(dir + @".\Server.txt").First();
                if (serverSet.Length < 300)
                {
                    connectString = serverSet;
                }
            }

            //System.Data.SqlClient.SqlConnection s = new System.Data.SqlClient.SqlConnection(sqlConnection.ConnectionString);

            //System.Data.EntityClient.EntityConnectionStringBuilder ecsb = new System.Data.EntityClient.EntityConnectionStringBuilder();
            //ecsb.Provider = "System.Data.SqlClient";              
            //ecsb.ProviderConnectionString = sqlConnection.ConnectionString;
            //ecsb.Metadata = @"res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl";
            //System.Data.EntityClient.EntityConnection ec = new System.Data.EntityClient.EntityConnection(ecsb.ConnectionString);

            //dbroomClientEntities dbroom = new dbroomClientEntities(ec);
            try
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("Select * from tblHostConfig;", connectString);
                System.Data.DataTable DT = new System.Data.DataTable();
                adapter.Fill(DT);
                adapter.Dispose();
                objUrl = "tcp://" + DT.Rows[0]["IP"] + ":" + DT.Rows[0]["Port"] + "/RoomObj";
                
                System.Runtime.Remoting.Channels.Tcp.TcpChannel tcp = new System.Runtime.Remoting.Channels.Tcp.TcpChannel(0);
                System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(tcp, false);
                //var HostData = (from o in dbroom.tblHostConfigs select o).First();
                //objUrl = "tcp://" + HostData.IP + ":" + HostData.Port + "/RoomObj";
            }
            catch (Exception )
            {
                throw new Exception("資料庫讀取失敗");
            }
            roomEvent.RoomEvent += new RoomEventHandler(RoomClient_RoomEvent);
            timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
            timer.Interval = 10000;
            timer.Start();           
        }

 private System.Data.DataSet MyDataSet()
 {
     System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(str_con);
     con.Open();
     da_1 = new System.Data.SqlClient.SqlDataAdapter(SQL_string, con);
     System.Data.DataSet data_set = new System.Data.DataSet();
     da_1.Fill(data_set, "Test");
     con.Close();
     return data_set;
 }

        public System.Data.DataSet consultaSimple(string sql_qry)
        {
            System.Data.SqlClient.SqlDataAdapter adaptador = new System.Data.SqlClient.SqlDataAdapter(sql_qry, con);

            System.Data.DataSet datos = new System.Data.DataSet();

            adaptador.Fill(datos, "resultado");

            return datos;
        }

 public DataTable Search_Data(string query)
 {
     Conexion conn = new Conexion();
     conn.Begin_conexion();
     System.Data.SqlClient.SqlDataAdapter qu;
     DataSet ds = new DataSet();
     qu = new System.Data.SqlClient.SqlDataAdapter(query, conn.conexion);
     qu.Fill(ds);
     conn.conexion.Close();
     return ds.Tables[0];
 }

        private System.Data.DataSet MyDataSet()
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
            con.Open();

            dataAdap1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
            System.Data.DataSet dataSet = new System.Data.DataSet();
            dataAdap1.Fill(dataSet, "Table_Data_1");
            con.Close();
            return dataSet;
        }

 public DataTable EjecutarProcedimientoAlmacenado(string nombreProceso,string datos)
 {
     Conexion conn = new Conexion();
     conn.Begin_conexion();
     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(nombreProceso);
     command.CommandType = CommandType.StoredProcedure;
     string cadenaProcedimiento = nombreProceso + datos;
     System.Data.SqlClient.SqlDataAdapter oAdapter = new System.Data.SqlClient.SqlDataAdapter(cadenaProcedimiento,conn.conex);
     DataTable oTabla = new DataTable();
     oAdapter.Fill(oTabla);
     return oTabla;
 }