DbHelper 操作SQL Server、Oracle、Access、Excel、CSV五种数据源+ Xml文件读写

编译环境：VS2008+Win7

数据源：

SQL Server 2005，Oracle 10g，Access 2003，Excel 2003，CSV，Xml

功能：

查看所有数据：读取数据表中所有数据显示在DataGridView中

查看学生总数：显示数据库中学生总数

插入一条记录：插入 003，李，23，0，1993-3-1记录

删除一条记录：删除userid为003的记录

修改一条记录：将userid为003的记录的usersex修改为1

读取/修改配置文件

DbHelper类用法

DOM模型操作Xml文件

 

示例源码下载地址：

 http://download.csdn.net/detail/daxiang12092205/4516239

 DbHelper类源码参考：

http://blog.csdn.net/charlsechang/article/details/6249284

 XML文件操作参考：

http://hi.baidu.com/wangweixp/blog/item/bca10dfa43732e9759ee90ff.html

 

读取修改配置文件源码：

        private void OperAppConfig()        {            //读取              string strName = ConfigurationManager.AppSettings["DbHelperProvider"];            //Console.WriteLine("------name1 is ：" + strName);            Configuration cfa = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);            //修改              cfa.AppSettings.Settings["DbHelperProvider"].Value = "System.Data.SqlClient";            cfa.AppSettings.Settings["DbHelperConnectionString"].Value = "Data Source=.\\sqlexpress;Initial Catalog=mytest;Integrated Security=True";            cfa.Save();            //操作结束刷新节点，否则下次取值不变，所有的操作未映射到磁盘文件App.Config            ConfigurationManager.RefreshSection("appSettings");            strName = ConfigurationManager.AppSettings["DbHelperProvider"];            //Console.WriteLine("------name2 is is：" + strName);        }

SQL Server删除数据源码：

        private void btnDelete_Click(object sender, EventArgs e)        {            try            {                DbHelper db = new DbHelper();                tsslStatus.Text = "正在删除数据";                string sqlInsert = "delete from [student] where userid='003' ";                DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);                int execresult = db.ExecuteNonQuery(dbcmd);                //Console.WriteLine("------:" + execresult);                if (execresult > 0)                {                    tsslStatus.Text = "删除成功";                }                else                {                    tsslStatus.Text = "删除失败";                }            }            catch (System.Data.SqlClient.SqlException)            {                tsslStatus.Text = "删除失败";            }        }

Oracle查看学生总数源码：

        private void btnGetNum_Click(object sender, EventArgs e)        {            try            {                DbHelper db = new DbHelper();                tsslStatus.Text = "正在获取数据";                string sqlInsert = "select count(*) from userinfo";                DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);                //不能用int execresult = (int)db.ExecuteScalar(dbcmd);出现InvalidCaseException异常                   object obj = db.ExecuteScalar(dbcmd);                int execresult = Convert.ToInt32(obj);                //Console.WriteLine("------:" + execresult);                if (execresult >= 0)                {                    tsslStatus.Text = "共计："+execresult.ToString();                }                else                {                    tsslStatus.Text = "获取失败";                }            }            catch (System.Data.OracleClient.OracleException ex)            {                //Console.WriteLine("------" + ex.Message);                tsslStatus.Text = "获取失败";            }        }

Access修改数据源码：

        private void btnModify_Click(object sender, EventArgs e)        {            try            {                DbHelper db = new DbHelper();                tsslStatus.Text = "正在修改数据";                string sqlInsert = "update [student] set usersex='1' where userid='003' ";                DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);                int execresult = db.ExecuteNonQuery(dbcmd);                //Console.WriteLine("------:" + execresult);                if (execresult > 0)                {                    tsslStatus.Text = "修改成功";                }                else                {                    tsslStatus.Text = "修改失败";                }            }            catch (System.Data.OleDb.OleDbException)            {                tsslStatus.Text = "修改失败";            }        }

Excel删除数据源码：

        private void btnDelete_Click(object sender, EventArgs e)        {            try            {                DbHelper db = new DbHelper();                tsslStatus.Text = "正在删除数据";                //Excel工作表中的行与真正的关系型数据库的行是有些不一样的，                  //删除Excel数据时，受到的限制要比从关系数据源中删除数据时更多,故不能用delete语句删除                  //string sqlInsert = "delete from [Sheet1$] where userid=1003 ";                //通过这种方法，这一行并没有真正删除掉，所以在select时要进行一定筛选，                  //如select * from [Sheet1$] where userid is not null                string sqlInsert = "update [Sheet1$] set userid=null,username=null,userage=null,usersex=null,userbirth=null where userid=1003";                DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);                int execresult = db.ExecuteNonQuery(dbcmd);                //Console.WriteLine("------:" + execresult);                if (execresult > 0)                {                    tsslStatus.Text = "删除成功";                }                else                {                    tsslStatus.Text = "删除失败";                }            }            catch (System.Data.OleDb.OleDbException)            {                tsslStatus.Text = "删除失败";            }        }

CSV删除数据源码：

        private void btnDelete_Click(object sender, EventArgs e)        {            DbHelper db = new DbHelper();            tsslStatus.Text = "正在删除数据";                        //csv不允许更新或删除记录，详细解释见：http://support.microsoft.com/kb/828892            //故不能用sql语句直接操作，采用 读取数据到datatable-修改datatable-删除源文件-导出datatable到新文件 方法              DataTable dt = new DataTable();            dt = GetAllData();            if (dt != null && dt.Rows.Count >= 0)            {                for (int i = 0; i < dt.Rows.Count;i++ )                {                    if (dt.Rows[i][0].ToString().Trim() == "1003")                    {                        dt.Rows.RemoveAt(i);                        break;                    }                }                if (File.Exists(filepath))                {                    File.Delete(filepath);                }                ExportToFile(dt);                tsslStatus.Text = "删除成功";            }            else            {                tsslStatus.Text = "删除失败";            }        }        /// <summary>        /// 获取csv文件所有数据，保存到DataTable中并返回         /// </summary>        /// <returns></returns>        private DataTable GetAllData()        {            try            {                DbHelper db = new DbHelper();                string sqlInsert = "select * from [student.csv]";                DbCommand dbcmd = db.GetSqlStringCommond(sqlInsert);                DataTable dt = db.ExecuteDataTable(dbcmd);                if (dt != null && dt.Rows.Count > 0)                {                    return dt;                }                else                {                    return null;                }            }            catch (Exception ex)            {                //Console.WriteLine("------ ex :" + ex.Message);                return null;            }        }        /// <summary>        /// 将DataTable数据导出到csv文件         /// </summary>        /// <param name="dataTable"></param>        public static void ExportToFile(DataTable dataTable)        {            try            {                using (StreamWriter sw = new StreamWriter(filepath, false, Encoding.Default))                {                    string repStr = ",";                    string colHead = string.Empty;                    for (int i = 0; i < dataTable.Columns.Count; i++)                    {                        colHead += dataTable.Columns[i].ColumnName + repStr;                    }                    if (colHead.Contains(repStr))                    {                        colHead = colHead.Remove(colHead.LastIndexOf(repStr));                    }                    sw.WriteLine(colHead);                    for (int i = 0; i < dataTable.Rows.Count; i++)                    {                        int cell = dataTable.Rows[i].ItemArray.Count();                        string content = string.Empty;                        for (int j = 0; j < cell; j++)                        {                            content += dataTable.Rows[i][j] + repStr;                        }                        if (content.Contains(repStr))                        {                            content = content.Remove(content.LastIndexOf(repStr));                        }                        sw.WriteLine(content);                    }                }            }            catch (Exception ex)            {                //Console.WriteLine("------ :" + ex.Message);            }        }

XML获取所有数据源码：

        private static string dirpath = Application.StartupPath + "\\";        private static string filepath = Application.StartupPath + \\student.xml;        private void btnGetAllData_Click(object sender, EventArgs e)        {            tsslStatus.Text = "正在获取";            //利用DOM模型读取xml文件              XmlDocument xmldoc = new XmlDocument();            XmlElement xmlelemF;            XmlElement xmlelemS1;            xmldoc.Load(filepath);            //将读取的数据保存到DataTable里              DataTable dt = new DataTable();            //获取用户数和属性数              int numF = xmldoc.DocumentElement.ChildNodes.Count;            int numS = xmldoc.DocumentElement.ChildNodes[0].ChildNodes.Count;            //Console.WriteLine("------num :" + numF+","+numS);            //获取所有的属性名，作为DataTable的列名              xmlelemF = (XmlElement)xmldoc.DocumentElement.ChildNodes[0];            for (int i = 0; i < numS; i++)            {                xmlelemS1 = (XmlElement)xmlelemF.ChildNodes[i];                dt.Columns.Add(xmlelemS1.LocalName, System.Type.GetType("System.String"));            }            //遍历所有节点，将节点值添加到DataTable中              for (int i = 0; i < numF; i++)            {                xmlelemF = (XmlElement)xmldoc.DocumentElement.ChildNodes[i];                DataRow dr = dt.NewRow();                for (int j = 0; j < numS; j++)                {                    xmlelemS1 = (XmlElement)xmlelemF.ChildNodes[j];                    //Console.WriteLine("------" +i+","+j+":"+ xmlelemS1.FirstChild.Value);                    dr[j] = xmlelemS1.FirstChild.Value;                }                dt.Rows.Add(dr);            }            //Console.WriteLine("------row num :" + dt.Rows.Count);            dgvStudent.DataSource = dt;            tsslStatus.Text = "获取成功";        }

细节问题：

1 CSV数据源，判断userid为1003记录是否存在时，语句select username from [student.csv] where userid=1003，其中1003不能改为‘1003’

2 csv不允许更新或删除记录，详细解释见：http://support.microsoft.com/kb/828892，

故不能用sql语句直接操作，采用 读取数据到datatable-修改datatable-删除源文件-导出datatable到新文件方法

3 Excel工作表中的行与真正的关系型数据库的行是有些不一样的，删除Excel数据时，受到的限制要比从关系数据源中删除数据时更多,故不能用delete语句删除。用update 表 set 字段=null 方法，通过这种方法，这一行并没有真正删除掉，所以在select时要进行一定筛选，如select * from [Sheet1$] where userid is not null

4 Oracle数据源修改数据时，不能用下面方法，执行结果obj为null，出现NullReferenceException

  object obj = db.ExecuteScalar(dbcmd);

  int execresult = Convert.ToInt32(obj);

可用 int execresult = db.ExecuteNonQuery(dbcmd);

5 Oracle数据源，命令语句如：select * from userinfo，userinfo不能写为[userinfo]，否则出现OracleException ORA-00903:表名无效异常

 

如有问题，欢迎留言。