SQL数据库操作方法

 
    //执行数据库语句
    public void ExecuteSql(string strSql)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
       
      
        SqlCommand cmd = new SqlCommand(strSql, conn);

        conn.Open();
        cmd.ExecuteNonQuery();

        conn.Close();

    }

//执行查询 

public DataTable ExcuteSelect(string strSql)
    {

        SqlConnection conn = new SqlConnection(ConnectionString);
    
        conn.Open();
        SqlCommand cmd = new SqlCommand(strSql, conn);
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();
        da.Fill(ds, "table");
        return ds.Tables["table"];
        conn.Close();
    }

  //是否有数据

    public bool HasData(string tablel)
    {

        SqlConnection conn = new SqlConnection(ConnectionString);
        string strSql="select * from ["+tablel+"]";
        conn.Open();
        SqlCommand cmd = new SqlCommand(strSql, conn);
        SqlDataReader dr = cmd.ExecuteReader();
        return dr.Read();
    
    }

  /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="db">数据库名</param>
        public void CreateDatabase(string db)
        {        
              if (HasDB(db))
               {
                 MessageBox.Show("数据库"+db+"已经存在");
               }
               else
               {
                
                try
                {
                    SqlConnection conn = new SqlConnection(ConnectionString);
                    string strSql = "create database ["+db+ "]";
                    SqlCommand cmd = new SqlCommand(strSql, conn);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("创建数据库"+db+ "成功");
                    conn.Close();
                  }
                    
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message, "创建数据库"+db+"失败");
                }
               
            }
        }
       
    
     /// <summary>
     /// 是否存在数据库
     /// </summary>
     /// <param name="db">数据库名</param>
     /// <returns></returns>
    public bool HasDB(string db)
    {
 
        SqlConnection conn = new SqlConnection(ConnectionString);
        string strSql = "SELECT name FROM sys.databases WHERE name = N'"+db+"';";
        conn.Open();
        SqlCommand cmd = new SqlCommand(strSql, conn);
        SqlDataReader dr = cmd.ExecuteReader();
        return dr.Read();
    
    }

 

    
        /// <summary>
        /// 从指定数据库和表中读取某一列的数据存成一个字符串数据,并以<tab>分隔
        /// </summary>
        /// <param name="db">数据库名</param>
        /// <param name="table_name">表名</param>
        /// <param name="column_name">字段名</param>
        /// <returns>返回一个字符串数据,并以<tab>分隔</returns>

        public string GetColumnData( string db,string table_name, string column_name)         
        {
            //返回值,错误时将返回空值
             string colunm_data = "";
            
            //连接字段值
            StringBuilder col_data = new StringBuilder();

            SqlConnection conn = new SqlConnection(ConnectionString);
           
            //查询语句
            string strSql = "use [" + db + "] select ["+column_name+"] from [" + table_name + "]";
   
       
            //创建sqldataadapter对象
            SqlDataAdapter adp = new SqlDataAdapter(strSql ,conn);
            //创建一个数据集
              DataSet ds = new DataSet();               
                try
                {
                    adp.Fill(ds, table_name);
                }
       
                catch
                {
                    MessageBox.Show ( "数据库查询错误，请重新设置查询条件");
                    adp.Dispose();
                    ds.Dispose();
            
                    return colunm_data;
                }
         
                if (ds.Tables[table_name].Rows.Count == 0)
                {
                     MessageBox.Show ( "在数据库中没有相应的记录");
                    return colunm_data;
                }
                for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
                {
                    //取出数据字段的值
                    col_data.Append(ds.Tables[table_name].Rows[i][column_name].ToString() + "/t");
               
                    colunm_data = col_data.ToString();
                }
                if (colunm_data == "")
                {
                    MessageBox.Show ("在数据库中没有相应的记录");
                }
                else
                {
                    MessageBox.Show ("查询成功");
                }
              
                return colunm_data;
                       
            }

/// <summary>
            /// 插入数据到指定索引列
            /// <param name="name">表名</param>
            /// <param name="i">列的索引值</param>
           /// <param name="data">数据</param>
            /// <returns></returns>

            public void InsertColumnData(string table, int i,string data)
            {
           
                try
                {
                    SqlConnection conn = new SqlConnection(ConnectionString);
                    string col="";
                    for (int j = 0; j < i; j++)
                    {
                        col +="' '"+",";
                  
                    }
                    col += "'" + data + "'";
                    string col1 = col;
                    string strSql = "insert into ["+table+"]  values("+col1+"); ";
                    SqlCommand cmd = new SqlCommand(strSql, conn);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("插入数据成功");
                    conn.Close();
                }

                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message, "插入数据失败");
                }
  

            }
  /// <summary>
            /// 根据某一列满足的指定条件去读取另一列需要的数据，并存成一个字符串数据
            /// </summary>
            /// <param name="table_name">表名</param>
            /// <param name="condition">条件</param>
            /// <param name="conditionColumn">满足条件的列字段名</param>
            /// <param name="column_name">需要的列字段名</param>
            /// <returns>返回一个字符串数据,并以<tab>分隔</returns>

        public string GetConditionColumnData(string table_name,string condition, string conditionColumn, string column_name)
            {
                //返回值,错误时将返回空值
                string column_data = "";

                //连接字段值
                StringBuilder col_data = new StringBuilder();

                SqlConnection conn = new SqlConnection(ConnectionString);

                //查询语句
                string strSql = " select [" + column_name + "] from [" + table_name + "] where "+conditionColumn + "='"+condition+"'";

                //创建sqldataadapter对象
                SqlDataAdapter adp = new SqlDataAdapter(strSql, conn);
                //创建一个数据集
                DataSet ds = new DataSet();
                try
                {
                    adp.Fill(ds, table_name);
                }

                catch
                {
                    MessageBox.Show("数据库查询错误，请重新设置查询条件");
                    adp.Dispose();
                    ds.Dispose();

                    return column_data;
                }

                if (ds.Tables[table_name].Rows.Count == 0)
                {
                    MessageBox.Show("在数据库中没有相应的记录");
                    return column_data;
                }
                for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
                {
                    //取出数据字段的值
                    col_data.Append(ds.Tables[table_name].Rows[i][column_name].ToString() + "/t");

                    column_data = col_data.ToString();
                }
                if (column_data == "")
                {
                    MessageBox.Show("在数据库中没有相应的记录");
                }
                else
                {
                    MessageBox.Show("查询成功");
                }

                return column_data;

            }

            /// <summary>
            ///取得一个表中某一列中所有不同的值，返回一个数组~
            /// </summary>
            /// <param name="table_name">表名</param>
            /// <param name="column_name">列字段名</param>
            /// <returns>返回一个数组</returns>
       
           public ArrayList GetDistinctColumnData(string table_name, string column_name)
            {
                 //返回的数组
                ArrayList column_data = new ArrayList();
          

                SqlConnection conn = new SqlConnection(ConnectionString);

                //查询语句
                string strSql = " select distinct [" + column_name + "] from [" + table_name + "] ";

                //创建sqldataadapter对象
                SqlDataAdapter adp = new SqlDataAdapter(strSql, conn);
                //创建一个数据集
                DataSet ds = new DataSet();
                try
                {
                    adp.Fill(ds, table_name);
                }

                catch
                {
                    MessageBox.Show("数据库查询错误，请重新设置查询条件");
                    adp.Dispose();
                    ds.Dispose();

                    return null ;
                }
                if (ds.Tables[table_name].Rows.Count == 0)
                {
                    MessageBox.Show("在数据库中没有相应的记录");
                    return null ;
                }
                for (int i = 0; i < ds.Tables[table_name].Rows.Count; i++)
                {
                 
                        //取出数据字段的值
                        column_data.Add((ds.Tables[table_name].Rows[i][column_name].ToString()));

                   
                }
                if (column_data.Count ==0 )
                {
                    MessageBox.Show("在数据库中没有相应的记录");
                }
                else
                {
                    MessageBox.Show("查询成功");
                }

                return column_data;

            }