postgreSQL的COPY (Bulk Data Transfer)

/***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","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");           
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!