udatalink.cs

dhtmlxGrid是DHTMLX公司的一个非常不错的JAVASCRIPT的网格组件

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Collections.Specialized;
using System.Collections;
/// <summary>
/// Summary description for uDataLink
/// </summary>
public class uDataLink
{
    protected XmlDocument xmlDoc = null;
    protected SqlConnection conn = null;
    protected String _connectionString = "";
    public String ConnectinString
    {
        get
        {
            return this._connectionString;
        }
        set
        {
            this._connectionString = value;
        }
    }

    protected String tag = "";
    public uDataLink()
	{
	}

    public uDataLink(String xmlFile, String connStr)
    {
        this.ConnectinString = connStr;
        this.xmlDoc = new XmlDocument();
        this.xmlDoc.Load(xmlFile);
        conn = new SqlConnection(this.ConnectinString);
        conn.Open();
    }
	~uDataLink()
	{
		
	}

    private String PrintXMLHeader()
    {
        return this.PrintXMLHeader("data", "");
    }
    private String PrintXMLHeader(String tag, String add)
    {
        this.tag = tag;
        return "<?xml version=\"1.0\" encoding=\"UTF-8\"?><"+tag+" "+add+" >";
    }
    private String PrintXMLFooter()
    {
        return "</" + this.tag + ">";
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="action"></param>
    /// <param name="sid"></param>
    /// <param name="tid"></param>
    /// <returns></returns>
    private String PrintAction(String action,String sid, String tid)
    {
        return "<action type='"+action+"' sid='"+sid+"' tid='"+tid+"'/>";
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public String Save(NameValueCollection data)
    {
		
        String res = this.PrintXMLHeader();
        ////
        String action = (xmlDoc.GetElementsByTagName("action")[0] == null) ? "" : data[xmlDoc.GetElementsByTagName("action")[0].InnerText];
        if (xmlDoc.DocumentElement.Attributes["type"] != null &&
            xmlDoc.DocumentElement.Attributes["type"].Value == "tree")
        {
            switch (action)
            {
                case "deleted":
                    res += this.DeleteTree(data);
                    break;
                case "inserted":
                    res += this.InsertTree(data);
                    break;
                default:
                    res += this.UpdateTree(data);
                    break;
            }
        }
        else
        {
            switch (action)
            {
                case "deleted":
                    res += this.Delete(data);
                    break;
                case "inserted":
                    res += this.Insert(data);
                    break;
                default:
                    res += this.Update(data);
                    break;
            }
        }
        res += this.PrintXMLFooter();
		conn.Close();
        return res;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String Delete(NameValueCollection data)
    {
        String tableName = xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
        String tableKey = xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value;
        String tableKeyValue = data[xmlDoc.GetElementsByTagName("key")[0].InnerText];
        if (tableName != ""){
            String sql="DELETE FROM "+tableName+" WHERE "+tableKey+"='"+tableKeyValue+"'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            return this.PrintAction("delete", tableKeyValue, "0");
        }
        return "";
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String Insert(NameValueCollection data)
    {
        String strA = "";
        String strB = "";
        int count = 0;
        foreach (XmlElement param in xmlDoc.GetElementsByTagName("param"))
        {
            if (count++ > 0)
            {
                strA += ",";
                strB += ",";
            }
            strA += param.Attributes["name"].Value;
            strB += "'"+data[param.InnerText]+"'";
        }
        String sql="INSERT INTO  "+xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value+" ("+strA+") VALUES ( "+strB+" ); SELECT scope_identity();";
        SqlCommand cmd = new SqlCommand(sql, conn);
        object res = cmd.ExecuteScalar();
        return this.PrintAction("insert", data[xmlDoc.GetElementsByTagName("key")[0].InnerText], (res != null)? res.ToString(): "0");
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String Update(NameValueCollection data)
    {
        String sql = "UPDATE " + xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value + " SET ";
        int count = 0;
        foreach (XmlElement param in xmlDoc.GetElementsByTagName("param"))
        {
            if (count++ > 0)
                sql += ",";
            sql += param.Attributes["name"].Value + " = '"+data[param.InnerText]+"'";
        }
        sql += " WHERE " + xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value + " ='" + data[xmlDoc.GetElementsByTagName("key")[0] .InnerText] + "'";
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        return this.PrintAction("update", data[xmlDoc.GetElementsByTagName("key")[0].InnerText], "0");
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String InsertTree(NameValueCollection data)
    {
        String table  = xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
        String id = xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value;
        String pid = xmlDoc.GetElementsByTagName("pkey")[0].Attributes["name"].Value;
        String order = xmlDoc.GetElementsByTagName("order")[0].Attributes["name"].Value;
        String name = xmlDoc.GetElementsByTagName("text")[0].Attributes["name"].Value;

        String a_table= xmlDoc.GetElementsByTagName("table")[0].InnerText;
        String a_id   = xmlDoc.GetElementsByTagName("key")[0].InnerText;
        String a_pid  = xmlDoc.GetElementsByTagName("pkey")[0].InnerText;
        String a_order= xmlDoc.GetElementsByTagName("order")[0].InnerText;
        String a_name = xmlDoc.GetElementsByTagName("text")[0].InnerText;

        String sql="UPDATE "+table+" SET " + order + "=" + order + "+1 WHERE " + pid+"="+data[a_pid]+" AND " + order + " > " + (Int32.Parse(data[a_order])-1);
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        sql = "INSERT INTO " + table+"("+ pid+","+ order+","+ name+") VALUES(" +data[a_pid]+","+ data[a_order]+",'"+data[a_name]+"');SELECT scope_identity();";
        cmd.CommandText = sql;
        object res = cmd.ExecuteScalar();
        return this.PrintAction("insert",data[a_id],(res == null)? "0":res.ToString());
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String UpdateTree(NameValueCollection data)
    {
        String table  = xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
        String id = xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value;
        String pid = xmlDoc.GetElementsByTagName("pkey")[0].Attributes["name"].Value;
        String order = xmlDoc.GetElementsByTagName("order")[0].Attributes["name"].Value;
        String name = xmlDoc.GetElementsByTagName("text")[0].Attributes["name"].Value;

        String a_table= xmlDoc.GetElementsByTagName("table")[0].InnerText;
        String a_id   = xmlDoc.GetElementsByTagName("key")[0].InnerText;
        String a_pid  = xmlDoc.GetElementsByTagName("pkey")[0].InnerText;
        String a_order= xmlDoc.GetElementsByTagName("order")[0].InnerText;
        String a_name = xmlDoc.GetElementsByTagName("text")[0].InnerText;


        String sql="SELECT * FROM "+table+" WHERE "+id+"="+data[a_id];
        SqlCommand cmd = new SqlCommand(sql, conn);
		SqlCommand cmd2 = new SqlCommand(sql, conn);
		
        SqlDataReader sdata = cmd.ExecuteReader();        
        sdata.Read();

        if ((sdata[pid].ToString() != data[pid]) || (sdata[order].ToString() != data[a_order]))
        {
            sql="UPDATE "+table+" SET "+order+"="+order+"-1 WHERE "+pid+"="+sdata[pid]+" AND "+order+" > "+sdata[order];
            cmd2.CommandText = sql;
            sdata.Close();
            cmd2.ExecuteNonQuery();

            sql="UPDATE "+table+" SET "+order+"="+order+"+1 WHERE "+pid+"="+data[a_pid]+" AND "+order+" > "+(Int32.Parse(data[a_order])-1);
            cmd2.CommandText = sql;
            cmd2.ExecuteNonQuery();
        }
        sdata.Close();
        sql = "UPDATE "+table+" SET "+pid+"="+data[a_pid]+","+order+"="+data[a_order]+","+name+"='"+data[a_name]+"'";
        sql +=" WHERE "+id+"="+data[a_id];
        cmd2.CommandText = sql;
        cmd2.ExecuteNonQuery();

        return this.PrintAction("update",data[a_id],data[a_id]);
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    private String DeleteTree(NameValueCollection data)
    {
        String table  = xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
        String id = xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value;
        String pid = xmlDoc.GetElementsByTagName("pkey")[0].Attributes["name"].Value;
        String order = xmlDoc.GetElementsByTagName("order")[0].Attributes["name"].Value;
        String name = xmlDoc.GetElementsByTagName("text")[0].Attributes["name"].Value;

        String a_id   = xmlDoc.GetElementsByTagName("key")[0].InnerText;
   
        String sql="DELETE FROM "+table+" WHERE "+id+"="+data[a_id];
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        sql = "SELECT " + id + " FROM " + table + " WHERE "+pid+"="+data[a_id];
        cmd.CommandText = sql;
        SqlDataReader r = cmd.ExecuteReader();
        ArrayList values = new ArrayList();        
        while (r.Read()){
            values.Add(new Hashtable());
            for (int i=0; i < r.FieldCount; i++)
                (values[values.Count-1] as Hashtable).Add(r.GetName(i),r.GetValue(i));
        }
        r.Close();
        foreach (Hashtable row in values)
        {
            NameValueCollection val = new NameValueCollection();
            val.Add(a_id, row[id].ToString());
            this.DeleteTree(val);
        }
        return this.PrintAction("delete",data[a_id],data[a_id]);
    }
    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    public String getXML()
    {
        String res = "";
        String type = (xmlDoc.DocumentElement.Attributes["type"] != null) ? xmlDoc.DocumentElement.Attributes["type"].Value : "";
        switch (type)
        {
            case "tree":
                String id = xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value;
                String pid = xmlDoc.GetElementsByTagName("pkey")[0].Attributes["name"].Value;
                String order = xmlDoc.GetElementsByTagName("order")[0].Attributes["name"].Value;
                String table = xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
                String name = xmlDoc.GetElementsByTagName("text")[0].Attributes["name"].Value;
                String userdata = "";
				res += this.PrintXMLHeader("tree","id='0'");
                res += this.GetXmlTree(table, id, pid, name, userdata, "0", order);
                break;
            default:
				res += this.PrintXMLHeader("rows","");
                res += this.GetXmlGrid();
                break;
        }
        res += this.PrintXMLFooter();
		conn.Close();
        return res;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    private String GetXmlGrid()
    {
        String res = "";
        String where = xmlDoc.GetElementsByTagName("where")[0].InnerText;
        String sql = "SELECT * FROM " + xmlDoc.GetElementsByTagName("table")[0].Attributes["name"].Value;
        if (where != "")
            sql += " WHERE " + where;
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader r = cmd.ExecuteReader();
        while (r.Read())
        {
            res += "<row id=\"" + r[xmlDoc.GetElementsByTagName("key")[0].Attributes["name"].Value] + "\">";
            
				foreach (XmlElement param in xmlDoc.GetElementsByTagName("param"))
				{
					res += "<cell>" + r[param.Attributes["name"].Value] + "</cell>";					
				}
                
            res += "</row>";
        }
        return res;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="table"></param>
    /// <param name="id"></param>
    /// <param name="pid"></param>
    /// <param name="name"></param>
    /// <param name="userdata"></param>
    /// <param name="sid"></param>
    /// <param name="order"></param>
    /// <returns></returns>
    protected String GetXmlTree(String table, String id, String pid, String name, String userdata, String sid, String order)
    {
        String sql = "SELECT "+id+","+pid+","+name+" FROM "+table+" WHERE "+pid+"="+sid+" ORDER BY "+order;
        String res="";
        SqlCommand cmd = new SqlCommand(sql,conn);
        SqlDataReader r = cmd.ExecuteReader();
		ArrayList values = new ArrayList();
        while (r.Read()){
            values.Add(new Hashtable());
            for (int i=0; i < r.FieldCount; i++)
                (values[values.Count-1] as Hashtable).Add(r.GetName(i),r.GetValue(i));
        }
        r.Close();
        foreach (Hashtable row in values)
        {
            res += "<item id='"+row[id].ToString()+"' text='"+row[name].ToString()+"'>";
            res += this.GetXmlTree(table, id, pid, name, userdata, row[id].ToString(), order);
            if (userdata != "")
                res += "<userdata name='" + userdata + "'>" + row[userdata] + "</userdata>";
            res += "</item>";
        }
        return res;
    }
}