akira32 編程之家 點部落

	string connectionString = ConfigurationManager.ConnectionStrings["SQLConnection1"].ConnectionString;
	using (SqlConnection connect = new SqlConnection(connectionString))
	{
		int rowNum = 0;

		try
		{
			connect.Open();


			string SQL = "select top 1 * from SourceTbl";

			DataTable dtSource = GetDataTable(connect, SQL);
			DataTable dt = dtSource.Clone();

			string csvData = File.ReadAllText(strFilePath);

			foreach (string row in csvData.Split('\n'))
			{
				if (rowNum == 0)
				{
					rowNum++;
					continue;
				}

				if (!string.IsNullOrEmpty(row))
				{
					DataRow dr = dt.NewRow();

					int i = 0;

					string[] array = row.Split(',');

					foreach (string cell in array)
					{
						string cell2 = cell;

						if (i == array.Length - 1)
						{
							cell2 = cell.Replace('\r', ' ').Trim();
						}

						dr[i] = cell2;
						System.Diagnostics.Debug.WriteLine("cell=" + cell);

						i++;
					}

					dt.Rows.Add(dr);
				}

				rowNum++;
			}

			connect.Close();
			return dt;
		}
		catch (Exception ex)
		{
			TextBoxErrorMessage.Text = "the row number " + rowNum.ToString() + " has some problem , ex = " + ex.Message;

			System.Diagnostics.Debug.WriteLine("  Message: ", ex.Message);
		}

		connect.Close();
	}

	return null;
}

private bool InsertCSVRecords(DataTable csvdt)
{
	string connectionString = ConfigurationManager.ConnectionStrings["SQLConnection1"].ConnectionString;
	using (SqlConnection connect = new SqlConnection(connectionString))
	{
		connect.Open();

		SqlTransaction transaction;
		transaction = connect.BeginTransaction("SampleTransaction");

		//creating object of SqlBulkCopy    
		using (SqlBulkCopy objbulk = new SqlBulkCopy(connect, SqlBulkCopyOptions.Default, transaction))
		{
			//assigning Destination table name    
			objbulk.DestinationTableName = "SourceTbl";

			//inserting Datatable Records to DataBase    
			try
			{
				objbulk.WriteToServer(csvdt);
				transaction.Commit();
				connect.Close();

				return true;
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine("Commit Exception Type: " + ex.GetType());
				System.Diagnostics.Debug.WriteLine("  Message: " + ex.Message);

				try
				{
					transaction.Rollback();
					connect.Close();

					TextBoxErrorMessage.Text = "import CSV failture ,recover data ,ex=" + ex.Message;
				}
				catch (Exception ex2)
				{
					connect.Close();

					System.Diagnostics.Debug.WriteLine("Rollback Exception Type: " + ex2.GetType());
					System.Diagnostics.Debug.WriteLine("  Message: " + ex2.Message);
				}

				return false;
			}
		}

	}
}


public DataTable GetDataTable(SqlConnection Conn, string SQL)
{
	using (SqlDataAdapter myAdapter = new SqlDataAdapter(SQL, Conn))
	{
		using (DataSet dsN = new DataSet())
		{
			try
			{
				myAdapter.Fill(dsN, "TmpTable");
				return dsN.Tables["TmpTable"];
			}
			catch (Exception ex)
			{
				string a = ex.Message;
				return null;
			}
		}
	}
}