c# 多datatable 按条件合并datatable

最新推荐文章于 2023-04-21 14:46:21 发布

myl0197

最新推荐文章于 2023-04-21 14:46:21 发布

阅读量1.2k

点赞数 1

分类专栏：计算机文章标签： c# string 数据库 insert exception cmd

本文链接：https://blog.youkuaiyun.com/myl0197/article/details/6123654

版权

计算机专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一个使用OleDb批量插入DataTable数据到数据库的方法。首先检查目标日期是否存在记录，若存在则询问用户是否覆盖，之后执行删除和插入操作；若不存在，则直接插入数据。涉及OleDb连接、命令及参数设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public void InsertTable(DataTable table)
        {
            DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
            dtFormat.ShortDatePattern = "YYYY/MM/DD";
            DateTime dtCurDateMonthEndDate = Convert.ToDateTime(dtEnd.Text, dtFormat);
            string   sMonthEndDate = DateTime.Parse(dtCurDateMonthEndDate.ToString("yyyy-MM-01")).AddMonths(1).AddDays(-1).ToString();

            OleDbConnection conn = new OleDbConnection(conString);
            int ret = 0;

            string strSql = "SELECT COUNT(*) AS n from youpykc where [rq]=#" + DateTime.Parse(sMonthEndDate) + "#";
            OleDbCommand cmd = new OleDbCommand(strSql, conn);
            conn.Open();
            OleDbDataReader oleRed = cmd.ExecuteReader();
            if (oleRed.Read())
            {
                int i;
                i = int.Parse(oleRed["n"].ToString());
                if (i > 0)
                {

if (MessageBox.Show("该月份的的期末库存数已经存在，是否重新更新，请谨慎操作", "重要提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{

                        string cmdString = "delete from youpykc where [rq]=#" + sMonthEndDate + "#";
                        cmd = new OleDbCommand(cmdString, conn);
                        try
                        {
                            cmd.ExecuteNonQuery();
                            MessageBox.Show("成功的删除了本期末的库记录,请重新点击保存期末库存", "提示");
                            string strInsert = "INSERT INTO youpykc([ypmc],[ypxh],[sl],[rq]) values (@ypmc,@ypxh,@endNum,@rq)";

                            conn = new OleDbConnection(conString);
                            OleDbCommand com = new OleDbCommand(strInsert, conn);
                            OleDbParameter p1 = new OleDbParameter("@ypmc", OleDbType.VarChar, 10);
                            OleDbParameter p2 = new OleDbParameter("@ypxh", OleDbType.VarChar, 10);
                            OleDbParameter p3 = new OleDbParameter("@endNum", OleDbType.Double, 10);
                            OleDbParameter p4 = new OleDbParameter("@rq", OleDbType.DBDate);
                            com.Parameters.Add(p1);
                            com.Parameters.Add(p2);
                            com.Parameters.Add(p3);
                            com.Parameters.Add(p4);
                            com.CommandType = CommandType.Text;
                            conn.Open();
                            try
                            {

                                for (int z = 0; z < table.Rows.Count; z++)
                                {
                                    DataRow row = table.Rows[z];

                                    p1.Value = row["ypmc"].ToString();
                                    p2.Value = row["ypxh"].ToString();
                                    p3.Value = row["endNum"].ToString();
                                    p4.Value = sMonthEndDate;

                                    ret += com.ExecuteNonQuery();
                                }
                                com.Dispose();
                                conn.Close();
                                MessageBox.Show("成功的将本期油品期末库存数保存到数据库中", "提示");
                            }
                            catch (System.Data.SqlClient.SqlException err)
                            {
                                MessageBox.Show(err.ToString());
                            }
                            conn.Close();

                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
                else
                {

//没有该月的汇总数据在油品月库存YOUPYK表中，添加记录到数据库中
string strInsert = "INSERT INTO youpykc([ypmc],[ypxh],[sl],[rq]) values (@ypmc,@ypxh,@endNum,@rq)";

                    conn = new OleDbConnection(conString);
                    OleDbCommand com = new OleDbCommand(strInsert, conn);
                    OleDbParameter p1 = new OleDbParameter("@ypmc", OleDbType.VarChar, 10);
                    OleDbParameter p2 = new OleDbParameter("@ypxh", OleDbType.VarChar, 10);
                    OleDbParameter p3 = new OleDbParameter("@endNum", OleDbType.Double, 10);
                    OleDbParameter p4 = new OleDbParameter("@rq", OleDbType.DBDate);
                    com.Parameters.Add(p1);
                    com.Parameters.Add(p2);
                    com.Parameters.Add(p3);
                    com.Parameters.Add(p4);
                    com.CommandType = CommandType.Text;
                    conn.Open();
                    try
                    {

                        for (int z = 0; z < table.Rows.Count; z++)
                        {
                            DataRow row = table.Rows[z];

                            p1.Value = row["ypmc"].ToString();
                            p2.Value = row["ypxh"].ToString();
                            p3.Value = row["endNum"].ToString();
                            p4.Value = sMonthEndDate;

                            ret += com.ExecuteNonQuery();
                        }
                        com.Dispose();
                        conn.Close();
                        MessageBox.Show("成功的将本期油品期末库存数保存到数据库中", "提示");
                    }
                    catch (System.Data.SqlClient.SqlException err)
                    {
                        MessageBox.Show(err.ToString());
                    }
                    conn.Close();
                }

                 }

}