ITPub博客

首页 > 数据库 > 数据库开发技术 > 动态创建SQL Server数据库、表、存储过程等架构信息 (转)

动态创建SQL Server数据库、表、存储过程等架构信息 (转)

原创 数据库开发技术 作者:amyz 时间:2007-11-13 13:21:26 0 删除 编辑
动态创建SQL Server数据库、表、存储过程等架构信息 (转)[@more@]动态创建sql SERVER数据库、表、存储过程等架构信息 作者: 孟宪会 出自: XML.sz.luohuedu.NET/">【孟宪会之精彩世界】 发布日期: 2003-8-17 23:34:35

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法。所要增加的控件如下:

Imports System.Data Imports System.Data.SqlClient Public Class Form1 Inherits System.<a href="tag-129-1.html">windows</a>.Forms.Form Private ConnectionString As String = "Data <a href="tag-162-1.html">source</a>=.;Initial Catalog=;User Id=sa;Pass<a href="tag-127-1.html">word</a>=;" Private reader As SqlDataReader = Nothing Private conn As SqlConnection = Nothing Private cmd As SqlCommand = Nothing Private AlterTableBtn As System.Windows.Forms.Button Private sql As String = Nothing Private CreateOthersBtn As System.Windows.Forms.Button #Region " Windows 窗体设计器生成的代码 " '窗体重写处置以清理<a href="tag-260-1.html">组件</a>列表。 Protected Overloads Overr<a href="tag-351-1.html">ide</a>s Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub Public Sub New() MyBase.New() InitializeComponent() End Sub Private components As System.ComponentModel.IContainer Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Create<a href="tag-57-1.html">db</a>Btn As System.Windows.Forms.Button Friend WithEvents CreateTableBtn As System.Windows.Forms.Button Friend WithEvents CreateSPBtn As System.Windows.Forms.Button Friend WithEvents CreateViewBtn As System.Windows.Forms.Button Friend WithEvents btnAlterTable As System.Windows.Forms.Button Friend WithEvents btnCreateOthers As System.Windows.Forms.Button Friend WithEvents btnDropTable As System.Windows.Forms.Button Friend WithEvents btnViewData As System.Windows.Forms.Button Friend WithEvents btnViewSP As System.Windows.Forms.Button Friend WithEvents btnViewView As System.Windows.Forms.Button <System href="tag-268-1.html">bug</a>gerStepThrough()> Private Sub InitializeComponent() Me.CreateDBBtn = New System.Windows.Forms.Button() Me.CreateTableBtn = New System.Windows.Forms.Button() Me.CreateSPBtn = New System.Windows.Forms.Button() Me.CreateViewBtn = New System.Windows.Forms.Button() Me.btnAlterTable = New System.Windows.Forms.Button() Me.btnCreateOthers = New System.Windows.Forms.Button() Me.btnDropTable = New System.Windows.Forms.Button() Me.btnViewData = New System.Windows.Forms.Button() Me.btnViewSP = New System.Windows.Forms.Button() Me.btnViewView = New System.Windows.Forms.Button() Me.DataGrid1 = New System.Windows.Forms.DataGrid() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'CreateDBBtn ' Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9) Me.CreateDBBtn.Name = "CreateDBBtn" Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23) Me.CreateDBBtn.TabIndex = 0 Me.CreateDBBtn.Text = "创建数据库" ' 'CreateTableBtn ' Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9) Me.CreateTableBtn.Name = "CreateTableBtn" Me.CreateTableBtn.TabIndex = 1 Me.CreateTableBtn.Text = "创建表" ' 'CreateSPBtn ' Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9) Me.CreateSPBtn.Name = "CreateSPBtn" Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23) Me.CreateSPBtn.TabIndex = 2 Me.CreateSPBtn.Text = "创建存储过程" ' 'CreateViewBtn ' Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9) Me.CreateViewBtn.Name = "CreateViewBtn" Me.CreateViewBtn.TabIndex = 3 Me.CreateViewBtn.Text = "创建视图" ' 'btnAlterTable ' Me.btnAlterTable.Location = New System.Drawing.Point(441, 9) Me.btnAlterTable.Name = "btnAlterTable" Me.btnAlterTable.TabIndex = 4 Me.btnAlterTable.Text = "修改表" ' 'btnCreateOthers ' Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43) Me.btnCreateOthers.Name = "btnCreateOthers" Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23) Me.btnCreateOthers.TabIndex = 5 Me.btnCreateOthers.Text = "创建规则和索引" ' 'btnDropTable ' Me.btnDropTable.Location = New System.Drawing.Point(138, 43) Me.btnDropTable.Name = "btnDropTable" Me.btnDropTable.TabIndex = 6 Me.btnDropTable.Text = "删除表" ' 'btnViewData ' Me.btnViewData.Location = New System.Drawing.Point(351, 43) Me.btnViewData.Name = "btnViewData" Me.btnViewData.TabIndex = 7 Me.btnViewData.Text = "查看数据" ' 'btnViewSP ' Me.btnViewSP.Location = New System.Drawing.Point(230, 43) Me.btnViewSP.Name = "btnViewSP" Me.btnViewSP.Size = New System.Drawing.Size(104, 23) Me.btnViewSP.TabIndex = 8 Me.btnViewSP.Text = "查看存储过程" ' 'btnViewView ' Me.btnViewView.Location = New System.Drawing.Point(443, 43) Me.btnViewView.Name = "btnViewView" Me.btnViewView.TabIndex = 9 Me.btnViewView.Text = "查看视图" ' 'DataGrid1 ' Me.DataGrid1.DataMember = "" Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location = New System.Drawing.Point(20, 76) Me.DataGrid1.Name = "DataGrid1" Me.DataGrid1.Size = New System.Drawing.Size(500, 183) Me.DataGrid1.TabIndex = 10 ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(538, 281) Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _ Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, Me.btnAlterTable, _ Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn}) Me.Name = "Form1" Me.Text = "动态创建<a href="leibie-8-1.html">SQL SERVER</a>数据库、表、存储过程等架构信息" CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region ' 创建数据库 Private Sub CreateDBBtn_Click(ByVal sender As System.<a href="tag-252-1.html">object</a>, ByVal e As System.EventArgs) _ Handles CreateDBBtn.Click conn = New SqlConnection(ConnectionString) ' 打开连接 If conn.State ConnectionState.Open Then conn.Open() End If 'MyDataBase为数据库名称 Dim sql As String = "CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, filename = " + _ "'D:MyDataBase.mdf', size=3," + "maxsize=5, filegrowth=10%) log on" + "(name=MyDataBase_log, " + _ "filename='D:MyDataBase.ldf',size=3," + "maxsize=20,filegrowth=1)" cmd = New SqlCommand(sql, conn) Try cmd.ExecuteNonQuery() Catch ae As SqlException MessageBox.Show(ae.Message.ToString()) End Try End Sub '创建表 Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles CreateTableBtn.Click conn = New SqlConnection(ConnectionString) ' 打开连接 If conn.State = ConnectionState.Open Then conn.Close() End If ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" conn.ConnectionString = ConnectionString conn.Open() sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + _ "myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)" cmd = New SqlCommand(sql, conn) Try cmd.ExecuteNonQuery() ' 添加纪录 sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ "VALUES (1001, _'【孟宪会之精彩世界】之一', 'http://xml.sz.luohuedu.net/', 100 ) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ "VALUES (1002, '【孟宪会之精彩世界】之二', 'http://www.erp800.com/net_lover/', 99) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ "VALUES (1003, '【孟宪会之精彩世界】之三', 'http://xml.sz.luohuedu.net/', 99) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ "VALUES (1004, '【孟宪会之精彩世界】之四', 'http://www.erp800.com/net_lover/', 100) " cmd = New SqlCommand(sql, conn) cmd.ExecuteNonQuery() Catch ae As SqlException MessageBox.Show(ae.Message.ToString()) End Try End Sub '创建存储过程 Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles CreateSPBtn.Click sql = "CREATE PROCEDURE myProc AS" + " <a href="tag-73-1.html">select</a> myName, myAddress FROM myTable GO" ExecuteSQLStmt(sql) End Sub '创建视图 Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles CreateViewBtn.Click sql = "CREATE VIEW myView AS SELECT myName FROM myTable" ExecuteSQLStmt(sql) End Sub '修改表 Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnAlterTable.Click sql = "ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())" ExecuteSQLStmt(sql) End Sub '创建规则和索引 Private Sub btnCreateOthers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnCreateOthers.Click sql = "CREATE UNIQUE INDEX " + "myIdx ON myTable(myName)" ExecuteSQLStmt(sql) sql = "CREATE RULE myRule " + "AS @myValues >= 90 AND @myValues <9999 table source=".;Initial" source="Catalog=MyDataBase;User" from source=".;Initial" source="Catalog=MyDataBase;User" source=".;Initial" source="Catalog=MyDataBase;User" from source=".;Initial" source="Catalog=MyDataBase;User"><BR></SPAN> <p style="clear:both;"></p> <p class="translate"> 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10752019/viewspace-982193/,如需转载,请注明出处,否则将追究法律责任。 </p> </div> </div> <div class="preview-footer clearfix"> <!--已点赞、收藏--> <div class="icon-praise" id="praise" onclick="blogObj.praise($(this),'/praise/')"><span class="text"></span><span class="num">0</span></div> <div class="icon-collect" id="collect" onclick="blogObj.praise($(this),'/favour/')"><span class="text"></span><span class="num">0</span></div> <div class="share-group fr"> <div class="bdsharebuttonbox bdshare-button-style0-16" data-bd-bind="1522216707615"> <span class="fl">分享到:</span> <a href="javascript:;" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a> <a href="javascript:;" class="bds_sqq" data-cmd="sqq" title="分享到QQ好友"></a> <a href="javascript:;" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a> <a href="javascript:;" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a> </div> <script> window._bd_share_config = { "common": { "bdSnsKey": {}, "bdText": "", "bdDesc": "", "bdMini": "", "bdMiniList": false, "bdPic": "", "bdStyle": "0", "bdSize": "32" }, "share": {"bdSize": 16} }; with (document) 0[(getElementsByTagName('head')[0] || body).appendChild(createElement('script')).src = 'http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion=' + ~(-new Date() / 36e5)]; if ($(window).width() < 1320) { if ($(window).width() < 1200) { $(".sideslip").css({ "left": "20px", "margin-left": 0 }) } else { $(".sideslip").css({ "margin-left": "-590px" }) } }</script> </div> </div> <div class="article-context"> <div class="fl"> <span class="color77">上一篇:</span> <a href="http://blog.itpub.net/10752019/viewspace-982192/">程序人生(二) (转)</a> </div> <div class="fr"> <span class="color77">下一篇:</span> <a href="http://blog.itpub.net/10752019/viewspace-982194/">Win98下安装JSP环境(tomcat-4.0.1 在 jdk1.3.1 下) (转)</a> </div> </div> <input type="hidden" id="blogId" value="982193"> <!--评论开始--> <div class="blog-comment"> <div class="new-comment"> <input type="hidden" id="hid" name="hid" value="982193"> <input type="hidden" name="_token" value="lxilf1tvrxPKx2LUtvhwRpu2CcRGXrbDFVNClXxj"> <a class="avatar" href="javascript:;"> <img src="http://blog.itpub.net/images/user_pic_default.png"></a> <!--用户未登录--> <div class="sign-container"> <span>请登录后发表评论</span> <a class="btn btn-sign"> <a class="layui-btn ml30" onclick="blog.login(event)">登录</a> </a> </div> <!--用户已登录--> <textarea placeholder="请写下你的评论…" onfocus="blogObj.textareaFocus($(this),200)" maxlength=200></textarea> </div> <div class="comment-list"> <div class="top-title"><span class="all-comment">全部评论</span> <span class="colorbb ml20"></span> </div> <div class="comment-items" id="comment_items"> <script type="text/template" id='blog_template'> <%for(var i=0;i <items.length;i++){%> <div class="comment-item"> <div class="author"> <a target="_blank" href="/<%=items[i].uid%>" class="avatar"> <img src="<%=items[i].headimg%>" alt=""> </a> <div class="info"> <div class="name"><a target="_blank" href="/<%=items[i].uid%>"><%=items[i].username%></a> <span class="fr reply-btn" onclick="blogObj.newComment($(this),'<%=items[i].username%>',<%=items[i].id%>)">回复</span></div> <div class="time colorbb"><%=items[i].createtime%></div> </div> </div> <p class="comment-wrap"><%=items[i].content%></p> <%if(items[i].items.items.length) { %> <div class="sub-comment-list"> <%for(var j=0;j<items[i].items.items.length;j++){%> <div class="sub-comment"> <div class="color77 time-reply"> <span class="time"><%=items[i].items.items[j].createtime%></span> <span class="reply-btn fr" onclick="blogObj.newComment($(this),'<%=items[i].items.items[j].username%>',<%=items[i].items.items[j].id%>)">回复</span> </div> <p><a href="/<%=items[i].uid%>"><%=items[i].items.items[j].username%></a>&nbsp;&nbsp;&nbsp;回复&nbsp;&nbsp;&nbsp;<a href="/<%=items[i].touid%>"><%=items[i].items.items[j].tousername%></a>: <span class="content"><%=items[i].items.items[j].content%></span></p> </div> <%}%> <%if(items[i].items.total > 5) { %> <div class="more-comment"><span class="sub-comment-count">还有<i class="count"><%=items[i].items.total-5%></i>条评论</span><span class="more-comment-btn" onclick=blogObj.loadSubComment($(this),<%=items[i].id%>) data-count=1 data-flag=true>点击查看</span></div> <%}%> </div> <%}%> </div> <%}%> </script> </div> </div> </div> <!--评论结束--> </div> <script> </script> <div class="fr w290"> <!--作者信息开始--> <!--作者信息开始--> <div class="author-info right-fixed "> <div class="head-img"> <a href="http://blog.itpub.net/10752019/"><img src="http://account.itpub.net/api/avatar.php?uid=10752019" alt=""></a> </div> <div class="author-name"><a href="http://blog.itpub.net/10752019/">amyz</a></div> <div class="author-intro"> </div> <ul class="tree-list clearfix"> <li> <div class="item-tt">博文量</div> <a href="http://blog.itpub.net/10752019/"><span class="item-num blognum">3984</span></a> </li> <li> <div class="item-tt">访问量</div> <div class="item-num blogviewnum">7339099</div> </li> </ul> </div> <!--作者信息结束--> <script> var data = {uid: 10752019}; $._ajax({ url: '/getAuthorInfo/', data: data, type: 'get', dataType: 'json', success: function (data) { if (data.code == 200) { var data = data.data; $(".blognum").text(data.blognum); $(".blogviewnum").text(data.visitednum); } } }); </script> <!--作者信息结束--> <!--博文推荐开始--> <div class="blog-choice right-fixed"> <h3 class="choice-title">最新文章</h3> <ul class="newul"> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-1001318/" class="clearfix"> 防止关闭windows (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-1001315/" class="clearfix"> TList,AnsiString和DynamicArray的妙用 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-1001311/" class="clearfix"> 在OpenGL中使用各异向性纹理过滤 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-1001310/" class="clearfix"> 完成端口(上) (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-987000/" class="clearfix"> WebLogic的初步研究(2)--结构篇 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-986998/" class="clearfix"> 玩转Windows桌面图标 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-986997/" class="clearfix"> Dos下键盘的完全控制 ------- 一系列的BIOS级别的键盘控制函数! (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-986996/" class="clearfix"> 图像平滑滚动效果的VC实现 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-986995/" class="clearfix"> VC++初学者常见问题解惑 (转) </a> </li> <li class="new-item"> <a href="http://blog.itpub.net/10752019/viewspace-986993/" class="clearfix"> 例程详析动态链接库 (转) </a> </li> <ul> </div> <!--大牛精选结束--> </div> </div> <div class="icon-back-top" onclick="$('html,body').animate({scrollTop:0},'slow');"></div> <!--main部分结束--> <!--百度推送--> <script> (function(){ var bp = document.createElement('script'); var curProtocol = window.location.protocol.split(':')[0]; if (curProtocol === 'https') { bp.src = 'https://zz.bdstatic.com/linksubmit/push.js'; } else { bp.src = 'http://push.zhanzhang.baidu.com/push.js'; } var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(bp, s); })(); </script> <!--2019-10-21 12:47:35--> <!--footer部分开始--> <div class="blog-footer"> <div class="w1200 pr"> <div class="footer-links"> <a class="icon-blog icon-wx mr30"> <div class="wx-qrcode"> <img src="http://edu.itpub.net/images/qrcode.jpg" alt=""> </div> </a> <a href="https://weibo.com/itpub2001?from=myfollow_all" class="icon-blog icon-wb" target="_blank"></a> </div> <p class="footer-nav"> <a href="http://www.it168.com/bottomfile/it168.shtml" target="_blank">支持我们</a> <a href="http://www.it168.com/bottomfile/tgzn.shtml" target="_blank">作者招募</a> <a href="http://www.it168.com/bottomfile/sytk.shtml" target="_blank">用户协议</a> <a href="http://blog.itpub.net/31509949/viewspace-2157750/" target="_blank">FAQ</a> <a href="http://edu.itpub.net/contactus.html" target="_blank">Contact Us</a> <script src="https://s22.cnzz.com/z_stat.php?id=1274521965&web_id=1274521965" language="JavaScript"></script> </p> <p>北京盛拓优讯信息技术有限公司. 版权所有&nbsp;&nbsp;<a style="color:#777777;" target="_blank" href="http://beian.miit.gov.cn">京ICP备09055130号-4</a>&nbsp;&nbsp;北京市公安局海淀分局网监中心备案编号:11010802021510</p> <p>广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员</p> </div> </div> <!--footer部分结束--> <script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?5016281862f595e78ffa42f085ea0f49"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script> <!-- END STAT PV --> </body> </html>