C# SqlConnection.Close方法代码示例

        public DataSet getData(SqlConnection conn, SqlDataAdapter da, DataSet ds)
        {
            //don't forget to escape slashes
            string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\PROG34998\\finalProject\\Project1\\Store.mdf;Integrated Security=True;User Instance=True";
            try
            {
                conn = new SqlConnection(connString);
                conn.Open();

                string sql = "SELECT * FROM [tCustomer]";
                da = new SqlDataAdapter(sql, conn);

                ds = new DataSet();
                da.Fill(ds, "tCustomer");
                conn.Close();

            }
            catch (SqlException ex)
            {
                if (conn != null)
                {
                    conn.Close();
                }
                MessageBox.Show(ex.Message, "Error Retrieving from DataBase");
            }

            return ds;
        }

        protected void Booking_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            int bookingid;
            bookingid = Convert.ToInt32(e.CommandArgument);

            SqlConnection con = new SqlConnection(strconn);
            con.Open();

            SqlCommand cmd = new SqlCommand("Delete from schedule where id = '" + bookingid + "'", con);
            SqlCommand cmd2 = new SqlCommand("UPDATE  carOwner  SET   MyPoints =  MyPoints+200   where carOwner.ownerID = (Select schedule.OwnerID from schedule where id = '" + bookingid + "')", con);

            int res2 = cmd2.ExecuteNonQuery();
            int res = cmd.ExecuteNonQuery();
            if (res > 0 && res2 > 0)
            {
                Label5.Visible = true;
                Label5.Text = "Your booking has been cancelled and you have been refunded back with 200 points";
                con.Close();
            }
            else
            {
                Label5.Visible = true;
                Label5.Text = "Error in booking cancelling try again later";
                con.Close();
            }

            displayBooking();
        }

        protected void Load_Grid()
        {
            string connectionString;
            connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();

            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
            }
            catch (Exception)
            {
                con.Close();
                return;
                throw;
            }

            SqlCommand cmd = new SqlCommand("SELECT C.*, A.agent_name FROM Customers AS C INNER JOIN Users AS U ON C.user_id = U.user_id INNER JOIN Employees AS E ON E.user_id = U.user_id INNER JOIN Agents AS A ON A.agent_id = E.agent_id", con);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable tablo = new DataTable();
            tablo.Load(dr);

            CustomerGrid.DataSource = tablo.DefaultView;
            CustomerGrid.DataBind();

            dr.Close();
            con.Close();
        }

        /// <summary>
        /// 执行SQL语句返回影响的行数
        /// </summary>
        /// <param name="cmdText">SQL文本</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            int retVal = 0;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = new SqlCommand();

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                comm.Connection = conn;
                comm.CommandText = cmdText;
                comm.CommandTimeout = 600;
                retVal = comm.ExecuteNonQuery();
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                return retVal;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

 public requirements Insert(requirements id)
 {
     string ConnectionString = IDManager.connection();
     SqlConnection con = new SqlConnection(ConnectionString);
     try
     {
     con.Open();
     SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_REQUIREMENTS", con);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
     cmd.ExecuteReader();
     con.Close();
     con.Open();
     cmd = new SqlCommand("SP_DMCS_GET_REQUIREMENTS", con);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
     SqlDataReader rdr = cmd.ExecuteReader();
     if (rdr.HasRows)
     {
         rdr.Read();
         id.req_id = rdr.GetInt32(0);
     }
     }
     catch (Exception ex)
     {
     id.SetColumnDefaults();
     }
     finally
     {
     con.Close();
     }
     return id;
 }

        protected long Execute(string procName, SqlParameter[] param)
        {
            long retVal = -1;
            SqlConnection con = new SqlConnection(GetConnection());
            SqlCommand cmd = null;
            try
            {

                con.Open();
                cmd = new SqlCommand();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(param);
                cmd.Connection = con;
                retVal = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception)
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
            return retVal;
        }

 /// <summary>
 /// Panels the type identifier.
 /// </summary>
 /// <param name="panelTypeCode">The panel type code.</param>
 /// <returns></returns>
 
 public int PanelTypeId(string panelTypeCode)
 {
     var panelTypeId = 1;
     using (var con = new SqlConnection(Properties.Settings.Default.ConnectionToSQL))
     {
         try
         {
             con.Open();
             var sqlCommand =
                 new SqlCommand(@"SELECT *  FROM [AirVents].[PanelType] WHERE[PanelTypeCode]   = '" + panelTypeCode + "'", con);
             var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
             var dataTable = new DataTable("panelTypeName");
             sqlDataAdapter.Fill(dataTable);
             panelTypeId =  Convert.ToInt32(dataTable.Rows[0]["PanelTypeID"]);
         }
         catch (Exception)
         {
             con.Close();
         }
         finally
         {
             con.Close();
         }
     }
     return panelTypeId;
 }

        private void button11_Click(object sender, EventArgs e)
        {
            string con = "server=Crazyboy-PC;Initial Catalog=master;Integrated Security=SSPI";
            string sql = "select Cbill,Cstyle,Csum,Ctime,CID,Gno from cash where Ctime='" + dateTimePicker2.Text.ToString() + "'";
            SqlConnection mycon = new SqlConnection(con);
            SqlCommand cmd = new SqlCommand(sql, mycon);
            if (mycon.State == ConnectionState.Closed)
                mycon.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dataGridView1.DataSource = 0;
            if (dr.HasRows)
            {
                BindingSource bs = new BindingSource();
                bs.DataSource = dr;
                this.dataGridView1.DataSource = bs;
                dataGridView1.Columns[0].HeaderCell.Value = "账单号";
                dataGridView1.Columns[1].HeaderCell.Value = "消费类型";
                dataGridView1.Columns[2].HeaderCell.Value = "消费金额";
                dataGridView1.Columns[3].HeaderCell.Value = "消费时间";
                dataGridView1.Columns[4].HeaderCell.Value = "收银员";
                dataGridView1.Columns[5].HeaderCell.Value = "客房号";
            }

            mycon.Close();

            //关闭连接并释放资源
            if (ConnectionState.Open == mycon.State)
            {
                mycon.Close();
            }
            mycon.Dispose();
        }

        protected void ButtonEnviar_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistroConnectionString"].ConnectionString);
                conn.Open();
                String contar_mensajes = "select count(*) from Mensaje_privado";
                SqlCommand command = new SqlCommand(contar_mensajes, conn);
                int contador = Convert.ToInt32(command.ExecuteScalar().ToString());
                conn.Close();

                conn.Open();
                String enviar_mensaje = "insert into Mensaje_privado (id_mensaje, id_remitente, id_buzon, leido, mensaje, asunto, fecha_de_envio) values (" + contador + ", " + Bandeja_Entrada.id_usuario + ", " + Bandeja_Entrada.id_destino + ", " + 0 + ", '" + TextBoxEM.Text + "', '" + LabelAsunto.Text + "', CURRENT_TIMESTAMP)";
                command = new SqlCommand(enviar_mensaje, conn);
                command.ExecuteNonQuery();
                Response.Write("Mensaje enviado!");
                Panel1.Visible = false;
                ButtonVolver.Visible = true;

                conn.Close();
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>
        /// Performs a scalar select and returns the value as a string
        /// </summary>
        /// <param name="qS"></param>
        /// <returns></returns>
        public static String ScalarString(String qS)
        {
            object returnValue = "";
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(qS, con);

            using (con)
            {
                if (con.State == ConnectionState.Open)
                {
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
                else
                {
                    con.Open();
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
            }

            if (returnValue == null)
            {
                return "";
            }
            else
                return returnValue.ToString();
        }

 public List<ItemTarefa> buscaItensTarefa(int id)
 {
     List<ItemTarefa> listaItens = new List<ItemTarefa>();
     SqlConnection conexao = new SqlConnection();
     conexao.ConnectionString = StaticObjects.strConexao;
     SqlCommand comando = new SqlCommand();
     SqlDataReader leitor;
     try
     {
         conexao.Open();
         comando.CommandText = @"SELECT id,idTarefa,data,descricao FROM dbo.ItensTarefa WHERE idTarefa = " + id + " ";
         comando.Connection = conexao;
         leitor = comando.ExecuteReader();
         while (leitor.Read())
         {
             ItemTarefa it = new ItemTarefa();
             it.id = Convert.ToInt16(leitor["id"]);
             it.idTarefa = Convert.ToInt16(leitor["idTarefa"]);
             it.data = Convert.ToDateTime(leitor["data"]);
             it.descricao = leitor["descricao"].ToString();
             listaItens.Add(it);
         }
         conexao.Close();
         return listaItens;
     }
     catch (Exception)
     {
         conexao.Close();
         return null;
     }
 }

        public Task Send(Message[] messages)
        {
            if (messages == null || messages.Length == 0)
            {
                return TaskAsyncHelper.Empty;
            }

            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(_connectionString);
                connection.Open();
                using (var cmd = new SqlCommand(_insertSql, connection))
                {
                    cmd.Parameters.AddWithValue("Payload", JsonConvert.SerializeObject(messages));

                    return cmd.ExecuteNonQueryAsync()
                        .Then(() => connection.Close()) // close the connection if successful
                        .Catch(ex => connection.Close()); // close the connection if it explodes
                }
            }
            catch (SqlException)
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                throw;
            }
        }

 public bool AddProduct(ProductsDto productsDto)
 {
     var cn = new SqlConnection(GetConnection());
     SqlTransaction trx = null;
     var isInsert = false;
     try
     {
         cn.Open();
         trx = cn.BeginTransaction();
         string cmdText = " insert into Products(ProductName,ProductCategory,MemberAnaylst,OrganizationName,TenureId,StyleResearchId,StrategyId,FrequencyCall,FrequencyCallType,Logo,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) " +
                          "values('" + productsDto.ProductName + "'," + productsDto.ProductCategory + "," + productsDto.MemberAnaylst + ",'" + productsDto.OrganizationName + "'," + productsDto.TenureId + "," + productsDto.StyleResearchId + "," + productsDto.StrategyId + "," + productsDto.FrequencyCall + ",'" + productsDto.FrequencyCallType + "','" + productsDto.Logo + "'," + productsDto.CreateUserId + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + productsDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();";
         var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
         var productId = Convert.ToInt32(cmd.ExecuteScalar());
         foreach (var documents in productsDto.ProductDocumentDtos)
             (new SqlCommand("insert into ProductDocument(ProductId,FileName,DocumentName) values( " + productId + ",'" + documents.FileName + "','" + documents.DocumentName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
         trx.Commit();
         isInsert = true;
         cn.Close();
     }
     catch (Exception)
     {
         if (trx != null) trx.Rollback();
         cn.Close();
     }
     return isInsert;
 }

        private void TryDBConnect()
        {
            bool IsConnect = false;

            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            XLog.Write("正在连接数据库...");
            try
            {
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    IsConnect = true;
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                IsConnect = false;
                connection.Close();
            }

            if (IsConnect == false)
            {
                DialogResult result = MessageBox.Show("无法连接至数据库服务器,请确认配置是否正确,网络是否畅通？", "无法连接至数据库服务器...", MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning);
                if (result == DialogResult.Retry)
                {
                    TryDBConnect();
                }
            }
        }

        public string insertandUpdate( string commandString)
        {
            string result = null; ;
            string connectonString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
              //  string commandString = String.Format("SELECT username FROM t_user where username='{0}'", username);
            SqlConnection sqlConnection = new SqlConnection(connectonString);
            try
            {
                sqlConnection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = commandString;
                int num = command.ExecuteNonQuery();
                if (num>0)
                result = "[email protected]执行成功";
                else
                    result = "[email protected]未找到值";
                sqlConnection.Close();

            }
            catch (Exception e)
            {
                result = "[email protected]" + e.Message;
            }
            finally
            {
                sqlConnection.Close();
            }

            return result;
        }