postgreSQL的COPY (Bulk Data Transfer)

为什么80%的码农都做不了架构师？>>>   

/***SQL Server***/

class SqlBulkCopyTest
{
      private static string connectionString = @"Data Source=(local)\InstanceName;Database=DataBaseName;Integrated Security=true";
      public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dataTable)
      {
           
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dataTable);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
       }

       static void Main(string[] args)
       {
            SqlConnection myConnection = new SqlConnection(connectionString);
            myConnection.Open();
            List<MyData> dataList = new List<MyData>();
            for(int i = 1; i < 100001; i++)
            {
                MyData data = new MyDat();
                data.GradeID = i;
                data.ClassID= i;
                data.StudentID = i;
                data.StudentName = "aaa" + i;
                dataList.Add(data);
            }
            try
            {
                DataTable dataTable = new DataTable("RM_MyData");
                dataTable.Columns.Add("GradeID", Type.GetType("System.Int32"));
                dataTable.Columns.Add("ClassID", Type.GetType("System.Int32"));
                dataTable.Columns.Add("StudentID", Type.GetType("System.Int32"));
                dataTable.Columns.Add("StudentName", Type.GetType("System.String"));
                foreach (MyData data in dataList)
                {
                    dataTable.Rows.Add(new Object[] { data.GradeID, data.ClassID, data.StudentID, data.StudentName});
                }
                SqlBulkCopyByDatatable(connectionString, "RM_MyData", dataTable);
            }
            finally
            {
                myConnection.Close();
            }
        }  
            
    }
}

/***postgre***/

class NpgsqlCopyTest
{
    private static string connectionString = String.Format("Server={0};Database={1};uid={2};pwd={3};Port={4};", "127.0.0.1", "RM_MyData", "postgres", "abc123!@#", "5432");
    public static void CopyMyData(List<MyData> dataList, string tableName)
    {
	// Import data to data table
	NpgsqlConnection conn = new NpgsqlConnection(connectionString);
	conn.Open();
	try
	{
		string copyString = "COPY " + "\"" + tableName + "\"" + " (\"GradeID\", \"ClassID\", \"StudentID\", \"StudentName\") FROM STDIN (FORMAT BINARY)";
		using (var writer = conn.BeginBinaryImport(copyString))
		{
			foreach (MyData myData in dataList)
			{
				writer.StartRow();
				writer.Write(myData.GradeID, NpgsqlDbType.Integer);
				writer.Write(myData.ClassID, NpgsqlDbType.Integer);
				writer.Write(myData.StudentID, NpgsqlDbType.Integer);
				writer.Write(myData.StudentName, NpgsqlDbType.Varchar);
			}
		}
	}
	catch (NpgsqlException sqlEx)
	{
		Console.WriteLine(sqlEx.Message);
	}
	catch (Exception e)
	{
		Console.WriteLine(e.Message);
		throw;
	}
	finally
	{
		conn.Close();
	}
    }

    static void Main(string[] args)
    {
        List<MyData> dataList = new List<MyData>();
        for(int i = 1; i < 100001; i++)
        {
            MyData data = new MyData();
            data.GradeID = i;
            data.ClassID= i;
            data.StudentID = i;
            data.StudentName = "aaa" + i;
            dataList.Add(data);
         }
         CopyMyData(dataList, "RM_MyData");           
    }
}

 

转载于:https://my.oschina.net/mj23/blog/750140