写在类里面的sql语句综合

把sql语句写在类里面在以后的使用中直接调用，不仅提高了代码的重用率，也很好的简化了页面的代码复杂度，其实现在大多数公司都是这样做的，自己没有刻意去寻找是否网上已经有这样的代码，既然自己写了，就贴出来给大家批评指正吧。

下面是三个最基本的语句（SELECT、UPDATE、INSERT）

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// SQL 的摘要说明
/// </summary>
public class SQL
{
 public SQL()
 {
  //
  // TODO: 在此处添加构造函数逻辑
  //
 }

    //Insert返回bool型，传回插入是否成功
    public bool Insert(string table,string[] data,string[] values)//表名，字段，添加的值
    {
        string strdata="";
        string strvalues="";
        foreach(string temp in data)
        {
            strdata =strdata + temp +",";
            strvalues =strvalues + "@" + temp +",";
        }
        strdata = strdata.Remove(strdata.Length-1);
        strvalues = strvalues.Remove(strvalues.Length-1);

        string dsn = ConfigurationManager.ConnectionStrings["friendConnectionString"].ToString();
        SqlConnection conn = null;
        conn = new SqlConnection(dsn);
        SqlCommand cmd = new SqlCommand("", conn);      
        string SqlStr = "INSERT ["+table+"]("+strdata+") VALUES("+strvalues+")";
        cmd.CommandText = SqlStr;
        int i=0;
        foreach(string temp in values)
        {
             cmd.Parameters.Add(new SqlParameter("@"+data[i],temp));
             i++;
        }    
        conn.Open();
        try
        {
            if (cmd.ExecuteNonQuery() == 0)
                return false;
            else
                return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

 

   //Select返回的是查询结果，放在数组里面
    public string[] Select(string table, string[] data,string whereid, string values)//表名、查找的参数、判断的条件字段、判断的条件的值
    {
        string[] getdata=new string[20];
        string strdata="";       
        foreach(string temp in data)
        {
            strdata =strdata + temp +",";           
        }
        strdata = strdata.Remove(strdata.Length-1);

        string dsn = ConfigurationManager.ConnectionStrings["friendConnectionString"].ToString();
        SqlConnection conn = null;
        conn = new SqlConnection(dsn);
        SqlCommand cmd = new SqlCommand("", conn);
        string SqlStr = "SELECT "+ strdata +" FROM [" + table + "] WHERE "+whereid+"="+"@"+whereid;
        cmd.CommandText = SqlStr;
        cmd.Parameters.Add(new SqlParameter("@" + whereid, values));       
        conn.Open();
        SqlDataReader MyReader = cmd.ExecuteReader();
        int i=0;
        if (MyReader.Read())
        {
            foreach (string temp in data)
            {
                getdata[i++] = MyReader[temp].ToString();
            }
        }
        return getdata;
    }

//Update返回的是bool型，判断是否更新成功   

 public bool Update(string table, string[] data, string[] values, string whereid, string realid)//表名，需要更新的字段，更新的值,更新判断的条件，条件的满足
    {
        string strdata = "";
       
        foreach (string temp in data)
        {
            strdata = strdata + temp + "=@"+ temp + ",";          
        }
        strdata = strdata.Remove(strdata.Length - 1);      

        string dsn = ConfigurationManager.ConnectionStrings["friendConnectionString"].ToString();
        SqlConnection conn = null;
        conn = new SqlConnection(dsn);
        SqlCommand cmd = new SqlCommand("", conn);
        string SqlStr = "UPDATE [" + table + "] SET " + strdata + " WHERE " + whereid + "=" + realid;
        cmd.CommandText = SqlStr;
        int i = 0;
        foreach (string temp in values)
        {
            cmd.Parameters.Add(new SqlParameter("@" + data[i], temp));
            i++;
        }
        conn.Open();
        try
        {
            if (cmd.ExecuteNonQuery() == 0)
                return false;
            else
                return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}