C# System.Data.SqlClient.SqlConnection.Close方法代码示例

        //Metodo de Iserción de Datos
        public bool Insertar(Comun.Vars p)
        {
            bool Resultado = false;
               //Realizar metodo de conexión
            System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection();
            //Activa o Desactivar String de Conexion
            cn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Wayvas\\Task\\Presentacion\\App_Data\\Task.mdf;Integrated Security=True;User Instance=True";
            //cn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=True";
            cn.Open();//Iniciamos la Conexion

            System.Data.SqlClient.SqlCommand cm =new System.Data.SqlClient.SqlCommand();
            cm.Connection=cn;
            cm.CommandType=System.Data.CommandType.Text;
            cm.CommandText = "Insert into Task(TaskName,StartDate,DueDate,CompDate,TaskComs) Values(@P_TaskName,@P_StartDate,@P_DueDate,@P_CompDate,@P_TaskComs)";

            cm.Parameters.AddWithValue("@P_TaskName", p.TaskName);
            cm.Parameters.AddWithValue("@P_StartDate", p.StartDate);
            cm.Parameters.AddWithValue("@P_DueDate", p.DueDate);
            cm.Parameters.AddWithValue("@P_CompDate", p.CompDate);
            cm.Parameters.AddWithValue("@P_TaskComs", p.TaskComs);

            int ra = cm.ExecuteNonQuery();
                   if (ra==1)
                   { Resultado=true;}
            cn.Close();
            cn.Dispose();
            return Resultado;
        }

        protected String fnActivitySelect(String strID)
        {
            String strTableBuild = "";
            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SQLSERVER_CONNECTION_STRING"].ToString());
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlDataReader dr;

            cmd.CommandText = "SELECT tblActivity.intActivityType, tblActivity.txtNote FROM tblActivity INNER JOIN tblContacts ON tblActivity.intContactId = tblContacts.id WHERE (tblContacts.sfid = '" + strID + "')";

            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = sqlConn;

            sqlConn.Open();

            dr = cmd.ExecuteReader();

            strTableBuild = strTableBuild + ("<div style=\"position:absolute; left: 400px; top: 275px;\">");
            strTableBuild = strTableBuild + ("<table border=1 bgcolor=silver style=\"border-collapse:collapse;\">");
            strTableBuild = strTableBuild + ("<th>Activity Type</th><th>Note</th>");
            while (dr.Read())
            {
                strTableBuild = strTableBuild + "<tr>";
                strTableBuild = strTableBuild + "<td>";
                strTableBuild = strTableBuild + fnConvertActivity(Convert.ToInt16(dr[0].ToString()));
                strTableBuild = strTableBuild + "</td>";
                strTableBuild = strTableBuild + "<td>";
                strTableBuild = strTableBuild + dr[1].ToString();
                strTableBuild = strTableBuild + "</td>";
                strTableBuild = strTableBuild + "</tr>";
            }
            strTableBuild = strTableBuild + ("</table>");
            strTableBuild = strTableBuild + ("</div>");
            sqlConn.Close();
            return strTableBuild;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            string connectionstring = "Data Source=devrystudentsp10.db.6077598.hostedresource.com;Persist Security Info=True;User ID=DeVryStudentSP10;Password=OidLZqBv4";
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = connectionstring;
            conn.Open();
            //MessageBox.Show(conn.State.ToString());
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "insert into Huber_Logon (email, lastname, firstname, password, userid)";
            comm.CommandText += " values (@email, @lastname, @firstname, @password, @userid)";

            comm.Parameters.AddWithValue("@email", txtemail.Text);
            comm.Parameters.AddWithValue("@lastname", txtLname.Text);
            comm.Parameters.AddWithValue("@firstname", txtfname.Text);
            comm.Parameters.AddWithValue("@password", txtpassword.Text);
            comm.Parameters.AddWithValue("@userid", txtuserid.Text);
            if (!dbLayer.isuseralreadyinthedbornot(txtuserid.Text, connectionstring))
            {
                comm.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("You Must enter another userid, that one is in use!");
            }

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

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objParameterItem"></param>
        /// <returns></returns>
        internal int AddNewVirtualDealerConfig(Business.ParameterItem objParameterItem)
        {
            int result = -1;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.VirtualDealerConfigTableAdapter adap = new DSTableAdapters.VirtualDealerConfigTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;
                result = int.Parse(adap.AddNewVirtualDealerConfig(-1, objParameterItem.Name, objParameterItem.SecondParameterID, objParameterItem.Code, objParameterItem.BoolValue,
                    objParameterItem.StringValue, objParameterItem.NumValue, objParameterItem.DateValue).ToString());
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="InvestorStatusID"></param>
        internal bool DeleteInvestorStatus(int InvestorStatusID)
        {
            bool Result = true;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.InvestorStatusTableAdapter adap = new DSTableAdapters.InvestorStatusTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;

                adap.DeleteInvestorStatus(InvestorStatusID);
            }
            catch (Exception ex)
            {
                Result = false;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            button1_Click(sender,e);
            int jiance = 1;
            try
            {
                System.Data.SqlClient.SqlConnection conn = null;
                conn = new System.Data.SqlClient.SqlConnection(Jingtai.ConnectionString);
                conn.Open();
                conn.Close();
            }
            catch
            {

                jiance = 0;
            }
            if (jiance == 0)
            {
                label3.Text = "检测失败";
                pictureBox1.BackColor = Color.Red;

            }
            else if (jiance == 1)
            {
                label3.Text = "检测通过";
                pictureBox1.BackColor = Color.Green;
            }
            else
            {
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        internal int AddNewInvestorStatus(string Name)
        {
            int Result = -1;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.InvestorStatusTableAdapter adap = new DSTableAdapters.InvestorStatusTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;

                Result = int.Parse(adap.AddNewInvestorStatus(Name).ToString());
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="InvestorAccountLogID"></param>
        /// <returns></returns>
        internal bool DeleteInvestorAccountLog(int InvestorAccountLogID)
        {
            bool Result = false;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.InvestorAccountLogTableAdapter adap = new DSTableAdapters.InvestorAccountLogTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;
                int resultDelete = adap.DeleteInvestorAccountLog(InvestorAccountLogID);
                if (resultDelete > 0)
                    Result = true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

 private void AutoSignOff()
 {
     try
     {
         bool autoOff = bool.Parse(ConfigurationManager.AppSettings["AutoSignOff"]);
         int autoOffTime = int.Parse(ConfigurationManager.AppSettings["AutoSignOffMinutes"]);
         string time = DateTime.Now.AddMinutes(-autoOffTime).ToString("yyyy-MM-dd HH:mm:ss");
         if (!autoOff)
             return;
         try
         {
             var con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Entities"].ConnectionString);
             var com = new System.Data.SqlClient.SqlCommand(string.Format("DELETE FROM UserOnline WHERE [TimeStamp] < '{0}'", time), con);
             con.Open();
             com.ExecuteNonQuery();
             try
             {
                 con.Close();
             }
             catch
             {
             }
         }
         catch
         {
         }
     }
     catch
     {
     }
 }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objVirtualDealer"></param>
        /// <returns></returns>
        internal string AddNewVirtualDealer(Business.VirtualDealer objVirtualDealer)
        {
            string result = "-1";
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            System.Data.SqlClient.SqlTransaction trans = null;
            DSTableAdapters.VirtualDealerTableAdapter virtualDealerAdap = new DSTableAdapters.VirtualDealerTableAdapter();
            DSTableAdapters.VirtualDealerConfigTableAdapter dealerConfigAdap = new DSTableAdapters.VirtualDealerConfigTableAdapter();
            DSTableAdapters.IVirtualDealerTableAdapter iVirtualDealerAdap = new DSTableAdapters.IVirtualDealerTableAdapter();
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();

                virtualDealerAdap.Connection = conn;
                virtualDealerAdap.Transaction = trans;
                int id = int.Parse(virtualDealerAdap.AddNewVirtualDealer(objVirtualDealer.Name, "").ToString());
                result = id.ToString();
                if (id == -1)
                {
                    throw new Exception("Data error");
                }

                dealerConfigAdap.Connection = conn;
                dealerConfigAdap.Transaction = trans;
                DateTime DateValue = DateTime.Parse("1753-01-01 00:00:00.000");
                dealerConfigAdap.Insert(null, "Profit max pip", id, "VD01", -1, "NaN", objVirtualDealer.ProfitMaxPip.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Loss max pip", id, "VD02", -1, "NaN", objVirtualDealer.LossMaxPip.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Min volume", id, "VD03", -1, "NaN", objVirtualDealer.StartVolume.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Max volume", id, "VD04", -1, "NaN", objVirtualDealer.EndVolume.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Delay", id, "VD05", -1, "NaN", objVirtualDealer.Delay.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Additional pip", id, "VD06", -1, "NaN", objVirtualDealer.AdditionalPip.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "Mode", id, "VD07", -1, "NaN", objVirtualDealer.Mode.ToString(), DateValue);
                dealerConfigAdap.Insert(null, "IsEnable", id, "VD08", objVirtualDealer.IsEnable ? 1 : 0, "NaN", "NaN", DateValue);
                dealerConfigAdap.Insert(null, "IsLimitAuto", id, "VD09", objVirtualDealer.IsLimitAuto ? 1 : 0, "NaN", "NaN", DateValue);
                dealerConfigAdap.Insert(null, "IsStopAuto", id, "VD10", objVirtualDealer.IsStopAuto ? 1 : 0, "NaN", "NaN", DateValue);
                dealerConfigAdap.Insert(null, "IsStopSlippage", id, "VD11", objVirtualDealer.IsStopSlippage ? 1 : 0, "NaN", "NaN", DateValue);
                dealerConfigAdap.Insert(null, "GroupCondition", id, "VD12", -1, objVirtualDealer.GroupCondition, "NaN", DateValue);
                dealerConfigAdap.Insert(null, "SymbolCodition", id, "VD13", -1, objVirtualDealer.SymbolCondition, "NaN", DateValue);
                iVirtualDealerAdap.Connection = conn;
                iVirtualDealerAdap.Transaction = trans;
                for (int i = 0; i < objVirtualDealer.IVirtualDealer.Count; i++)
                {
                    iVirtualDealerAdap.Insert(objVirtualDealer.IVirtualDealer[i].InvestorGroupID, objVirtualDealer.IVirtualDealer[i].SymbolID, id);
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return "Data error";
            }
            finally
            {
                virtualDealerAdap.Connection.Close();
                dealerConfigAdap.Connection.Close();
                iVirtualDealerAdap.Connection.Close();
                conn.Close();
            }
            return result;
        }

        public string Create(Customer customerEntity)
        {
            try
            {
                using(System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
                {
                    sqlConnection.Open();
                    string sqlQuery =
                        "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName, @LastName, @Address, @City)";
                    sqlConnection.Execute(sqlQuery,
                                          new
                                              {
                                                  customerEntity.FirstName,
                                                  customerEntity.LastName,
                                                  customerEntity.Address,
                                                  customerEntity.City,
                                              });
                    sqlConnection.Close();
                }
                return "Created";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public void Persist(IEnumerable<TradeRecord> trades)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=TradeDatabase;Integrated Security=True;"))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    foreach (var trade in trades)
                    {
                        var command = connection.CreateCommand();
                        command.Transaction = transaction;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "dbo.insert_trade";
                        command.Parameters.AddWithValue("@sourceCurrency", trade.SourceCurrency);
                        command.Parameters.AddWithValue("@destinationCurrency", trade.DestinationCurrency);
                        command.Parameters.AddWithValue("@lots", trade.Lots);
                        command.Parameters.AddWithValue("@price", trade.Price);

                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
                connection.Close();
            }

            logger.LogInfo("{0} trades processed", trades.Count());
        }

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

        /// <summary>
        /// 
        /// </summary>
        /// <param name="AgentID"></param>
        /// <param name="ListRoleID"></param>
        /// <returns></returns>
        internal int AddListPermit(int AgentID, List<int> ListRoleID)
        {
            int Result = -1;
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);
            DSTableAdapters.PermitTableAdapter adap = new DSTableAdapters.PermitTableAdapter();

            try
            {
                conn.Open();
                adap.Connection = conn;
                int count = ListRoleID.Count;
                for (int i = 0; i < count; i++)
                {
                    Result = int.Parse(adap.AddNewPermit(null, AgentID, ListRoleID[i]).ToString());
                }

            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return Result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        internal List<Business.LastBalance> GetData()
        {
            List<Business.LastBalance> result = new List<Business.LastBalance>();

            DSTableAdapters.LastAccountTableAdapter adap = new DSTableAdapters.LastAccountTableAdapter();
            DS.LastAccountDataTable tbLastBalance = new DS.LastAccountDataTable();
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBConnection.DBConnection.Connection);

            try
            {
                conn.Open();
                adap.Connection = conn;
                tbLastBalance = adap.GetData();

                result = this.MapLastBalance(tbLastBalance);
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                adap.Connection.Close();
                conn.Close();
            }

            return result;
        }