/// <summary> /// 查询数据库指定数据表字段列表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { this.listBox1 .Items.Clear(); string TableName = DbName.SelectedText.ToString(); CurrTb = CurrDb.Tables.Item(DbTables.SelectedIndex + 1, "dbo"); foreach (SQLDMO.Column curr in CurrTb.Columns) { this.listBox1.Items.Add(curr.Name.ToString()); } CreateQueryString(); }
private string GetTablesScript(string dtName) { try { SQLDMO.SQLServer oserver = new SQLDMO.SQLServer(); string connStr = KingTop.Common.SQLHelper.ConnectionStringLocalTransaction; string[] arrConn = connStr.Split(';'); string server = string.Empty; string database = string.Empty; string login = string.Empty; string password = string.Empty; for (int i = 0; i < arrConn.Length; i++) { string[] itemArr = arrConn[i].Split('='); if (itemArr[0].ToLower() == "server") { server = itemArr[1]; } else if (itemArr[0].ToLower() == "database") { database = itemArr[1]; } else if (itemArr[0].ToLower() == "uid") { login = itemArr[1]; } else if (itemArr[0].ToLower() == "pwd") { password = itemArr[1]; } } oserver.Connect(server, login, password); SQLDMO._Database mydb = oserver.Databases.Item(database, "owner"); SQLDMO._Table mytable = mydb.Tables.Item(dtName, "dbo"); string tableScript = mytable.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, null, null, SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default); tableScript = tableScript.Replace("[nvarchar] (0)", "[nvarchar] (max)"); tableScript = tableScript.Replace("[varchar] (-1)", "[varchar] (max)"); //去掉GO int lastPosNum = tableScript.LastIndexOf("GO"); if (lastPosNum > 0) { tableScript = tableScript.Substring(0, lastPosNum); } oserver.DisConnect(); //注释掉不需复制的字段 string[] arrFields = noCopyFields.Split(','); for (int i = 0; i < arrFields.Length; i++) { if (!string.IsNullOrEmpty(arrFields[i]) && tableScript.ToLower().IndexOf("[" + arrFields[i].ToLower() + "]") != -1) { tableScript = Regex.Replace(tableScript, "\\[" + arrFields[i] + "\\]", "--[" + arrFields[i] + "]", RegexOptions.IgnoreCase); } } return(tableScript.Replace("'", "''")); } catch { Response.Write("<div align=center style='padding:20px'>复制失败,原因是sqldmo.dll未注册,注册方法如下:<br><br> 打开开始,在运行中输入 regsvr32 \"C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\Binn\\sqldmo.dll\" 注册sqldmo.dll。<br><br>在注册前请确认sqldmo.dll是否存在,不存在请从网上下载sqldmo.dll到相应目录,再进行注册"); Response.Write("<br><br><a href=# onclick='history.back();'>[返回]</a></div>"); Response.End(); return(""); } }
/// <summary> /// 获取指定数据表字段信息 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button4_Click(object sender, EventArgs e) { listView1.Items.Clear(); int TableIndex = this.dataGridView1.CurrentRow.Index; string TableName = ds.Tables["dt"].Rows[TableIndex]["Name"].ToString(); CurrTb = CurrDb.Tables.Item(TableIndex + 1, "dbo"); foreach (SQLDMO.Column curr in CurrTb.Columns) { //ListViewItem it = new ListViewItem(curr.Name.ToString(), curr.Datatype.ToString(), curr.AllowNulls.ToString()); ListViewItem it = new ListViewItem(new string[] { curr.Name.ToString(), curr.Datatype.ToString(), curr.AllowNulls.ToString() }); this.listView1.Items.Add(it); } CreateQueryString(); }