1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel;
4: using System.Data;
5: using System.Data.Common;
6: using System.Data.Sql;
7: using System.Drawing;
8: using System.Text;
9: using System.Windows.Forms;
10: using System.Data.SqlClient;
11: using SMOObject = Microsoft.SqlServer.Management.Smo;
12:
13: namespace RefreshView
14: {
15: public partial class frmRefreshView : Form
16: {
17: public frmRefreshView()
18: {
19: InitializeComponent();
20: }
21:
22: //get the instance server
23: //this server object need reference to
24: //Microsoft.SqlServer.ConnectionInfo assembly
25: SMOObject.Server objServer =
26: new SMOObject.Server(".\\SQL2005");
27:
28: private void GetDatabaseName()
29: {
30: foreach (SMOObject.Database aDB in
31: objServer.Databases)
32: {
33: //check whether this database
34: //is system database or not
35: if (!aDB.IsSystemObject)
36: {
37: cboDatabase.Items.Add(aDB.Name);
38: }
39: }
40: }
41:
42: private void GetSchema(string DBName)
43: {
44: //set the database
45: SMOObject.Database aDB = objServer.Databases[DBName];
46:
47: //loop all schema
48: foreach (SMOObject.Schema aSchema in aDB.Schemas)
49: {
50: cboSchema.Items.Add(aSchema.Name);
51: }
52: if (cboSchema.Items.Count > 0)
53: { cboSchema.SelectedIndex = 0; }
54: }
55:
56: private void GetViews(string DBName,string Schema)
57: {
58: //set the database
59: SMOObject.Database aDB = objServer.Databases[DBName];
60:
61: //loop all view object in the current database
62: foreach (SMOObject.View aView in aDB.Views)
63: {
64: //show view object with appropiate schema
65: if (aView.Schema == Schema)
66: {
67: clbView.Items.Add(aView.Name);
68: }
69: }
70: if (clbView.Items.Count < 1)
71: { clbView.Items.Clear(); }
72: }
73:
74: private void CheckedAllView(bool boolChecked)
75: {
76: for (int i = 0; i <= clbView.Items.Count - 1; i++)
77: {
78: clbView.SetItemChecked(i, boolChecked);
79: }
80: }
81:
82: private void frmRefreshView_Load(object sender,
83: EventArgs e)
84: {
85: GetDatabaseName();
86: }
87:
88: private void cboDatabase_SelectedIndexChanged(object sender,
89: EventArgs e)
90: {
91: if (cboDatabase.SelectedIndex >= 0)
92: {
93: clbView.Items.Clear();
94: lbInfo.Items.Clear();
95: cboSchema.Items.Clear();
96:
97: GetSchema(cboDatabase.Text);
98: }
99: }
100:
101: private void chkSelectAll_CheckedChanged(object sender,
102: EventArgs e)
103: {
104: CheckedAllView(chkSelectAll.Checked);
105: }
106:
107: private void btnRefreshMetadata_Click(object sender,
108: EventArgs e)
109: {
110: using (SqlConnection sqlConn =
111: new SqlConnection("database=" + cboDatabase.Text +
112: ";server=.\\sql2005;uid=sa;pwd=sql2005"))
113: {
114: using (SqlCommand sqlCmd =
115: new SqlCommand())
116: {
117: sqlCmd.CommandType = CommandType.Text;
118: sqlCmd.Connection = sqlConn;
119:
120: sqlConn.Open();
121:
122: //clear listbox info
123: lbInfo.Items.Clear();
124:
125: for (int i = 0; i <= clbView.Items.Count - 1; i++)
126: {
127: if (clbView.GetItemChecked(i))
128: {
129: //use sp_refreshview stored proc
130: sqlCmd.CommandText = string.Format(
131: "EXEC sp_refreshview '{0}'",
132: clbView.GetItemText(clbView.Items[i]));
133:
134: try
135: {
136: //exec the stored proc
137: sqlCmd.ExecuteNonQuery();
138:
139: lbInfo.Items.Add("Metadata " +
140: clbView.GetItemText(
141: clbView.Items[i]) +" refreshed.");
142:
143: }
144: catch (SqlException sqlEx)
145: {
146: MessageBox.Show(sqlEx.Message);
147: return;
148: }
149:
150: }
151: }
152: }
153: }
154:
155: }
156:
157: private void clbView_DoubleClick(object sender,
158: EventArgs e)
159: {
160: txtInfo.ResetText();
161:
162: //use ScriptingOptions class
163: //to see the script
164: SMOObject.ScriptingOptions scriptOpt =
165: new SMOObject.ScriptingOptions();
166:
167: scriptOpt.IncludeHeaders = true;
168: scriptOpt.SchemaQualify = true;
169:
170: //get the database
171: SMOObject.Database aDB =
172: objServer.Databases[cboDatabase.Text];
173:
174: //get the view name from the checkedlistbox
175: //by double clicking its item
176: SMOObject.View aView =
177: aDB.Views[clbView.GetItemText(clbView.SelectedItem)];
178:
179: //it's a string collection
180: //loop each string
181: foreach (string aScript in aView.Script(scriptOpt))
182: {
183: txtInfo.Text += aScript + "\n";
184: }
185: }
186:
187: private void cboSchema_SelectedIndexChanged(object sender,
188: EventArgs e)
189: {
190: if (cboSchema.SelectedIndex >= 0)
191: {
192: clbView.Items.Clear();
193: GetViews(cboDatabase.Text,cboSchema.Text);
194: }
195: }
196:
197:
198: }
199:
200: }