求代码优化

Question

public bool SaveToDataBase(string filename)
        {
            List<List<object>> CitReport = ReaderCIT(filename);
            CitReport.RemoveAt(0);
            bool b = false;
            SqlConnection con = new SqlConnection(AppConfiguration.CITConnectionString);
            try
            {
                string sql = "insert into citreport values(";
                foreach (List<object> item in CitReport)
                {
                    for (int i = 0; i < item.Count; i++)
                    {
                        if (i == 0)
                        {
                            sql += "'" + Convert.ToString(item[i]) + "'";
                        }
                        else
                        {
                            sql += "," + "'" + Convert.ToString(item[i]) + "'";
                        }
                    }
                    break;
                }
                sql +=",'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                SqlCommand com = new SqlCommand(sql, con);
                con.Open();
                int sum = com.ExecuteNonQuery();
                com.Dispose();
                if (sum > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {                throw ex;
            }
            finally { con.Close(); con.Dispose(); }
            return b;
        }这段代码是导入一个最少几百条的Exel表到数据到数据库
我不会事务之类的东西，求高手优化一下  感激感激.....

Answer 1

1、去学习一下事务的使用。不复杂的。这个报错的可能性太高了。
2、这段代码的逻辑应该全是错的吧，SQL拼出来是什么样的您有输出来看过吗？
3、先要从0到1，再从1到100000。 即：先把结果弄出来，再考虑优化的事情有人回复了我的帖子，竟然没发现。用《Csdn收音机》告别烦恼！

Answer 2

sql server中直接导入EXCEL
读取EXCEL到dataset,sqldataadapter更新数据库
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
 using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
  OleConn.Open();
  String sql = "SELECT * FROM [Sheet1$]";
  OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
  DataSet ds= new DataSet();
  OleDaExcel.Fill(ds);
  OleConn.Close();
}

Answer 3

  我的sql语句没错谢谢
  foreach 下面加break 是让他直插入一条数据

Answer 4

 我加break 只是让他只插入第一条数据  
 如过不加就会所有的数据都在一个insert里面  这我知道

Answer 5

public bool SaveCITToDataBase(string filename)
        {
            List<List<object>> CitReport = ReaderCIT(filename);
            CitReport.RemoveAt(0);
            bool b = false;
            SqlConnection con = new SqlConnection(AppConfiguration.CITConnectionString);
            try
            {
                string sql = string.Empty;
                foreach (List<object> item in CitReport)
                {
                    sql += "insert into citreport values(";
                    for (int i = 0; i < item.Count; i++)
                    {
                        
                        if (i == 0)
                        {
                            sql += "'" + Convert.ToString(item[i]) + "'";
                        }
                        else
                        {
                            sql += "," + "'" + Convert.ToString(item[i]) + "'";
                        }
                        
                    }
                    sql +=",'"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
                }
                SqlCommand com = new SqlCommand(sql, con);
                con.Open();
                int sum = com.ExecuteNonQuery();
                com.Dispose();
                if (sum > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {                throw ex;
            }
            finally { con.Close(); con.Dispose(); }
            return b;
        }