SqlHelper与XML导数据

以操作学生表为例，演示基于XML文件的数据库导入导出操作。

1.1、SqlHelper – ExecuteReader（）方法准备：

注意事项：

1)、static string constr= ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;
2)、if (para != null&&para.Length>0)
3)、if (conn.State == System.Data.ConnectionState.Closed)
4)、res = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//SqlDataReader对象使用完毕后关闭SqlConnection连接

代码清单：

static class SQLHelper
    {
        //1 各方法共用的连接字符串
static string constr= ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;
        public static SqlDataReader ExecuteReader(string sqlcmd,params SqlParameter[] para)
        {
            SqlDataReader res = null;
            SqlConnection conn = new SqlConnection(constr);
            using(SqlCommand cmd = new SqlCommand(sqlcmd,conn))
            {
                //2 装配参数
                if (para != null&&para.Length>0)
                {
                    cmd.Parameters.AddRange(para); 
                }                    
                //3 打开数据库连接
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                //4 返回SqlDataReader对象在Main方法中被调用完关闭时，关闭SqlConnection连接
                res = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            return res;
        }
}

 

1.2、导出到XML文件

class Program
    {
        static void Main(string[] args)
        {
            XDocument doc = new XDocument();
            XElement ele = new XElement("root");
            string sql = @"select stuId, stuName, stuSex, stuBirthdate, stuStudydate, stuAddress, stuEmail, 

stuPhone, stuIsDel, stuInputtime, classId from Student";
            string[] strs = {"stuId", "stuName", "stuSex", "stuBirthdate", "stuStudydate", "stuAddress", 

"stuEmail", "stuPhone", "stuIsDel", "stuInputtime", "classId" };
            using (SqlDataReader reader = SQLHelper.ExecuteReader(sql))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        XElement stu = new XElement("student");
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            XElement stuChild = new XElement(strs[i]);
                            stuChild.Value = reader[i].ToString();
                            stu.Add(stuChild);
                        }
                        ele.Add(stu);
                    }
                    doc.Add(ele);
                    doc.Save("stuData.xml");
                }
            }
            Console.WriteLine("导出成功！");
        }
}

 

2.1、SQLHelper - ExecutNoquery（）

//方法准备

    public static int ExecutNoquery(string commandText, params SqlParameter[] para)
        {
            int res = 0;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    if (para != null && para.Length > 0)
                    {
                        cmd.Parameters.AddRange(para);
                    }
                    if (conn.State == System.Data.ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    res = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            return res;
        }

 

2.2、XML文件导入数据库

public static void ExportStudentToXML()
        {    
        //准备语句，定义参数
        string sql = @"insert into Student0818(stuName, stuSex, stuBirthdate, stuStudydate, 

stuAddress, stuEmail, stuPhone, stuIsDel, stuInputtime, classId) values(@stuName, @stuSex, @stuBirthdate, 

@stuStudydate, @stuAddress, @stuEmail, @stuPhone, @stuIsDel, @stuInputtime, @classId)";
         SqlParameter[] para = 
                {
                       new SqlParameter("@stuName",SqlDbType.NVarChar),
                       new SqlParameter("@stuSex",SqlDbType.Char),
                       new SqlParameter("@stuBirthdate",SqlDbType.DateTime),
                       new SqlParameter("@stuStudydate",SqlDbType.DateTime),
                       new SqlParameter("@stuAddress",SqlDbType.NVarChar),
                       new SqlParameter("@stuEmail",SqlDbType.NVarChar),
                       new SqlParameter("@stuPhone",SqlDbType.NVarChar),
                       new SqlParameter("@stuIsDel",SqlDbType.Bit),
                       new SqlParameter("@stuInputtime",SqlDbType.DateTime),
                       new SqlParameter("@classId",SqlDbType.Int)
                };
           //准备XML文档
           XDocument doc = XDocument.Load("stuData.xml");
           XElement root = doc.Root;
           int count = 0;
           //解析XML文档，插入数据库
           foreach (XElement row in root.Elements())
               {
                    List<string> list = new List<string>();
                    foreach (XElement ele in row.Elements())
                    {
                        list.Add(ele.Value);
                    }
                     for (int i = 0; i < list.Count-1; i++)
                    {
                        para[i].Value = list[i + 1]; //用.value,这里就不用考虑数据类型的转换了
                    }
                    SQLHelper.ExecutNoquery(sql,para);
                    count++;
               }
           Console.WriteLine("{0}行受影响",count);
    }