我编写了一个C＃控制台程序，该程序读取制表符分隔的文件并执行
bcp tblName + " in " + outputfilename + " -c -T -S servername\instancename
-U readonly -P readonly -F2";
第一列是
EmpID，它是int，第二列是EMPName，它是Varchar2(100)，EMPLOYER VARCHAR(200)。outputfilename有时会出错，而数据输入人员会忘记在第一栏中输入empID，有时甚至在输入员工姓名时会转到下一行。EMPID EMPNAME EMPLOYER
100 Ann Taylor A Corporation
Brian Tao B Corporation
200 Cindy
Smith C Corporation
400 Daryl John "D Corporation
第一行是正确的。
第二行，因为没有
EMPID，我想获取行号并将其记录为错误。第3行，尽管Smith是Cindy的姓氏，但无法知道，因此只能保存200个CIndy，但由于Smith前面没有数字，因此日志应报告错误
第4行缺少双引号，报告日志文件中的错误。
能做到吗？
谢谢
先生
最佳答案
AFAIK，使用BCP没有简单的方法。 BCP通常非常容忍输入文件的列/行格式中的不规则性。
由于您已经在使用C＃，因此我的建议是考虑使用SQLBulkCopy API代替。它几乎与BCP一样快，并将所有输入文件处理交给程序。最初的工作量比BCP多，但是一旦有了工作实例，就可以为程序员提供更多的灵活性和选择。
这个MSDN线程有sanka060707的示例，说明如何从c＃http://social.msdn.microsoft.com/Forums/en-US/4929a0a8-0137-45f6-86e8-d11e220048c3/creating-a-new-table-in-sql-server-from-adonet-datatable?forum=adodotnetdataproviders中使用它（这是对该问题的第五次答复）。这是它的副本（长）
public class SqlTableCreator
{
#region Instance Variables
private SqlConnection _connection;
public SqlConnection Connection {
get { return _connection; }
set { _connection = value; }
}
private SqlTransaction _transaction;
public SqlTransaction Transaction {
get { return _transaction; }
set { _transaction = value; }
}
private string _tableName;
public string DestinationTableName {
get { return _tableName; }
set { _tableName = value; }
}
#endregion
#region Constructor
public SqlTableCreator() { }
public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
public SqlTableCreator(SqlConnection connection, SqlTransaction transaction) {
_connection = connection;
_transaction = transaction;
}
#endregion
#region Instance Methods
public object Create(DataTable schema) {
return Create(schema, null);
}
public object Create(DataTable schema, int numKeys) {
int[] primaryKeys = new int[numKeys];
for (int i = 0; i < numKeys; i++) {
primaryKeysIdea = i;
}
return Create(schema, primaryKeys);
}
public object Create(DataTable schema, int[] primaryKeys) {
string sql = GetCreateSQL(_tableName, schema, primaryKeys);
SqlCommand cmd;
if (_transaction != null && _transaction.Connection != null)
cmd = new SqlCommand(sql, _connection, _transaction);
else
cmd = new SqlCommand(sql, _connection);
return cmd.ExecuteNonQuery();
}
public object CreateFromDataTable(DataTable table) {
string sql = GetCreateFromDataTableSQL(_tableName, table);
SqlCommand cmd;
if (_transaction != null && _transaction.Connection != null)
cmd = new SqlCommand(sql, _connection, _transaction);
else
cmd = new SqlCommand(sql, _connection);
return cmd.ExecuteNonQuery();
}
#endregion
#region Static Methods
public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys) {
string sql = "CREATE TABLE " + tableName + " (\n";
// columns
foreach (DataRow column in schema.Rows) {
if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))
sql += column["ColumnName"].ToString() + " " + SQLGetType(column) + ",\n";
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
string pk = "CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
if (hasKeys) {
// user defined keys
foreach (int key in primaryKeys) {
pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
}
}
else {
// check schema for keys
string keys = string.Join(", ", GetPrimaryKeys(schema));
pk += keys;
hasKeys = keys.Length > 0;
}
pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
if (hasKeys) sql += pk;
sql += ")";
return sql;
}
public static string GetCreateFromDataTableSQL(string tableName, DataTable table) {
string sql = "CREATE TABLE [" + tableName + "] (\n";
// columns
foreach (DataColumn column in table.Columns) {
sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
if (table.PrimaryKey.Length > 0) {
sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
foreach (DataColumn column in table.PrimaryKey) {
sql += "[" + column.ColumnName + "],";
}
sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
}
return sql;
}
public static string[] GetPrimaryKeys(DataTable schema) {
List<string> keys = new List<string>();
foreach (DataRow column in schema.Rows) {
if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
keys.Add(column["ColumnName"].ToString());
}
return keys.ToArray();
}
// Return T-SQL data type definition, based on schema definition for a column
public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale) {
switch (type.ToString()) {
case "System.String":
return "VARCHAR(" + ((columnSize == -1) ? 255 : columnSize) + ")";
case "System.Decimal":
if (numericScale > 0)
return "REAL";
else if (numericPrecision > 10)
return "BIGINT";
else
return "INT";
case "System.Double":
case "System.Single":
return "REAL";
case "System.Int64":
return "BIGINT";
case "System.Int16":
case "System.Int32":
return "INT";
case "System.DateTime":
return "DATETIME";
default:
throw new Exception(type.ToString() + " not implemented.");
}
}
// Overload based on row from schema table
public static string SQLGetType(DataRow schemaRow) {
return SQLGetType(schemaRow["DataType"],
int.Parse(schemaRow["ColumnSize"].ToString()),
int.Parse(schemaRow["NumericPrecision"].ToString()),
int.Parse(schemaRow["NumericScale"].ToString()));
}
// Overload based on DataColumn from DataTable type
public static string SQLGetType(DataColumn column) {
return SQLGetType(column.DataType, column.MaxLength, 10, 2);
}
#endregion
}
关于c# - C#bcp到SQL Server，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20499352/