由于Oracle数据库中表和字段名的特殊要求，需要将表名和字段名全部改为大写，才能准确的导入到Oracle中。
我的思路是首先在通过C#语言生成批处理的存储过程，然后执行存储过程。
在SQL sever中，修改表名和字段名的SQL语句格式如下图：
一下代码为生成批处理字符串的方法，并保存到了strtest中。
/// <summary>
/// 获取列名
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public List<string> GetTables(string connection)
{
List<string> tablelist = new List<string>();
SqlConnection objConnetion = new SqlConnection(connection);
try
{
if (objConnetion.State == ConnectionState.Closed)
{
objConnetion.Open();
DataTable objTable = objConnetion.GetSchema("Tables");
foreach (DataRow row in objTable.Rows)
{
tablelist.Add(row[2].ToString());
}
}
}
catch
{
}
finally
{
if (objConnetion != null && objConnetion.State == ConnectionState.Closed)
{
objConnetion.Dispose();
}
}
return tablelist;
}
/// <summary>
/// 获取字段
/// </summary>
/// <param name="connection"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public List<string> GetColumnField(string connection, string TableName)
{
List<string> Columnlist = new List<string>();
SqlConnection objConnetion = new SqlConnection(connection);
try
{
if (objConnetion.State == ConnectionState.Closed)
{
objConnetion.Open();
}
SqlCommand cmd = new SqlCommand("Select Name FROM SysColumns Where id=Object_Id('" + TableName + "')", objConnetion);
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
Columnlist.Add(objReader[0].ToString());
}
}
catch
{
}
objConnetion.Close();
return Columnlist;
}
private void button1_Click(object sender, EventArgs e)
{
string sqlstr;
string strConn;
strConn = "Server=PC--20101007WET\\SQLEXPRESS;Integrated Security=SSPI;Database=GxDataCollectorDB";
List<string> dd;
dd = GetTables(strConn);
int i;
int j;
string strTmp = "";
string LcaseName;
string UcaseName;
string strLcaseFieldName;
string strUcaseFieldname;
string strTest = "";
for (i = 0; i < dd.Count; i ++ )
{
LcaseName = dd[i].ToString();
List<string> cc;
cc = GetColumnField(strConn, LcaseName);
//重命名字段名
for (j = 0; j < cc.Count; j ++ )
{
strLcaseFieldName = cc[j].ToString();
strUcaseFieldname = strLcaseFieldName.ToUpper();
if (strLcaseFieldName != strUcaseFieldname)
{
sqlstr = "EXEC sp_rename '" + LcaseName + "." + strLcaseFieldName + "','" + strUcaseFieldname + "','COLUMN'";
strTest = strTest + sqlstr + "\r\n";
}
}
//重命名表名
//LcaseName = dd[i].ToString();
//UcaseName = LcaseName.ToUpper();
//if (LcaseName != UcaseName)
//{
// sqlstr = "EXEC sp_rename '" + LcaseName + "','" + UcaseName + "'";
// strTest = strTest + sqlstr + "\r\n";
//}
}
MessageBox.Show("成功！");
}
在生成后，将以上代码拷贝到SQL Server企业管理器的查询分析器中，并选择当前数据库，具体如下图所示：
然后执行就行，以上修改表名，修改字段名类似，不在赘述了。
yush
转载请注明出处
