这段代码实现功能:从dReader中取数批量插入表里,某条数据插入时判断是否在表中已存在,不存在才插入
已知主键值肯定不为空,传入的参数肯定有值
求解为什么会报错 值不能为空 参数名:commandTextpublic virtual int InsertDataReader(IDataReader dReader, string TableName, int batchRow)
{
DataTable dt = dReader.GetSchemaTable();
StringBuilder sb = new StringBuilder(); //取得插入表主键
var keysql = @"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME='{0}'";
keysql = String.Format(keysql, TableName);
var key = "";
var keylocation = -1;
var value = "";
if (m_targetDbType == DataBaseType.SqlServe)
key = SqlDB.ExecuteScalar(this.m_targetStr, keysql).ToString(); int ret = 0;
//标志,用来计数
int flag = 0;
string strSql = "";
while (dReader.Read())
{
flag = flag + 1; // 生成字段(仅生成一次)
if (strSql == "")
{
strSql = @" Insert Into " + TableName + " (";
StringBuilder sbKey = new StringBuilder();
for (int i = 0; i < dReader.FieldCount; i++)
{
//取主键的索引
if (m_targetDbType == DataBaseType.SqlServe && dt.Rows[i][0].ToString() == key)
keylocation = i;
sbKey.Append(dt.Rows[i][0].ToString() + ",");
}
sbKey.Remove(sbKey.Length - 1, 1);
strSql += sbKey.ToString() + ") Values (";
} // 生成值
StringBuilder sbValue = new StringBuilder();
string str = "";
for (int i = 0; i < dReader.FieldCount; i++)
{
str = GetStringByDataTypeForSql(dt.Rows[i][5].ToString(), dReader[i].ToString());
sbValue.Append(str + ",");
//取主键对应的值
if (i == keylocation)
value = dReader[i] == null ? "" : dReader[i].ToString();
}
sbValue.Remove(sbValue.Length - 1, 1); //检测将要插入的记录在数据库是否已经存在,不存在才插入
if (m_targetDbType == DataBaseType.SqlServe)
{
var checksql = "";
if (key != "")
{
checksql = @"select count(1) from {0} where {1}='{2}'";
checksql = String.Format(checksql, TableName, key, value);
}
var count = SqlDB.ExecuteNonQuery(this.m_targetStr, checksql);
if (count == 0)
{
sb.Append(strSql);
sb.Append(sbValue).Append(");");
}
} if (flag == batchRow)
{
if (m_targetDbType == DataBaseType.Oracle)
{
ret += OracleDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
else
{
ret += SqlDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
sb = new StringBuilder();
flag = 0;
}
}
if (flag != 0)
{
if (m_targetDbType == DataBaseType.Oracle)
{
ret += OracleDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
else
{
ret += SqlDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
}
return ret;
}
已知主键值肯定不为空,传入的参数肯定有值
求解为什么会报错 值不能为空 参数名:commandTextpublic virtual int InsertDataReader(IDataReader dReader, string TableName, int batchRow)
{
DataTable dt = dReader.GetSchemaTable();
StringBuilder sb = new StringBuilder(); //取得插入表主键
var keysql = @"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME='{0}'";
keysql = String.Format(keysql, TableName);
var key = "";
var keylocation = -1;
var value = "";
if (m_targetDbType == DataBaseType.SqlServe)
key = SqlDB.ExecuteScalar(this.m_targetStr, keysql).ToString(); int ret = 0;
//标志,用来计数
int flag = 0;
string strSql = "";
while (dReader.Read())
{
flag = flag + 1; // 生成字段(仅生成一次)
if (strSql == "")
{
strSql = @" Insert Into " + TableName + " (";
StringBuilder sbKey = new StringBuilder();
for (int i = 0; i < dReader.FieldCount; i++)
{
//取主键的索引
if (m_targetDbType == DataBaseType.SqlServe && dt.Rows[i][0].ToString() == key)
keylocation = i;
sbKey.Append(dt.Rows[i][0].ToString() + ",");
}
sbKey.Remove(sbKey.Length - 1, 1);
strSql += sbKey.ToString() + ") Values (";
} // 生成值
StringBuilder sbValue = new StringBuilder();
string str = "";
for (int i = 0; i < dReader.FieldCount; i++)
{
str = GetStringByDataTypeForSql(dt.Rows[i][5].ToString(), dReader[i].ToString());
sbValue.Append(str + ",");
//取主键对应的值
if (i == keylocation)
value = dReader[i] == null ? "" : dReader[i].ToString();
}
sbValue.Remove(sbValue.Length - 1, 1); //检测将要插入的记录在数据库是否已经存在,不存在才插入
if (m_targetDbType == DataBaseType.SqlServe)
{
var checksql = "";
if (key != "")
{
checksql = @"select count(1) from {0} where {1}='{2}'";
checksql = String.Format(checksql, TableName, key, value);
}
var count = SqlDB.ExecuteNonQuery(this.m_targetStr, checksql);
if (count == 0)
{
sb.Append(strSql);
sb.Append(sbValue).Append(");");
}
} if (flag == batchRow)
{
if (m_targetDbType == DataBaseType.Oracle)
{
ret += OracleDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
else
{
ret += SqlDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
sb = new StringBuilder();
flag = 0;
}
}
if (flag != 0)
{
if (m_targetDbType == DataBaseType.Oracle)
{
ret += OracleDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
else
{
ret += SqlDB.ExecuteNonQuery(this.m_targetStr, sb.ToString());
}
}
return ret;
}
运行时没办法调试
而本地连不到Oracle数据库,没办法取数,所以也没法调试
所以请高手帮忙推断一下哪里出的问题
最好把程序代码分成一小段一小段来测试,比如说,在执行对 key 赋值的语句后,先 return key,看是否获得了你需要的数据,如果不是,则上面这段语句有问题.如果是,则继续做类似的事情,向下再分出一小段来.
这是程序调试的基本方法,相信楼主也用过,则是糊涂了吧.
checksql = @"select count(1) from {0} where {1}='{2}'";
checksql = String.Format(checksql, TableName, key, value);
执行上面拼出来的SQL时出错
public int ExecuteNonQuery(string connectionString, string commandText);commandText不能为空 就是执行ExecuteNonQuery时但是checksql 明明不是空
public int ExecuteNonQuery(string connectionString, string commandText);
后面直接返回 connectionString 和 commandText 看对不对.