using system;
using system.collections.generic;
using system.componentmodel;
using system.data;
using system.drawing;
using system.linq;
using system.text;
using system.windows.forms;
using microsoft.sqlserver.management.common;//需添加microsoft.sqlserver.connectioninfo.dll的引用
using microsoft.sqlserver.management;//
using microsoft.sqlserver.management.smo;//在microsoft.sqlserver.smo.dll中
using microsoft.sqlserver.management.smo.registeredservers;//microsoft.sqlserver.smoextended
using microsoft.sqlserver.management.smo.broker;
using microsoft.sqlserver.management.smo.agent;
using microsoft.sqlserver.management.smo.sqlenum;
using microsoft.sqlserver.management.smo.mail;
using microsoft.sqlserver.management.smo.internal;
using system.io;
using system.data.sqlclient;
using system.text;
using system.text.regularexpressions;
////引用位置： c:\program files\microsoft sql server\100\sdk\assemblies\
/// <summary>
/// 涂聚文 2017-06-02
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_click(object sender, eventargs e)
{
//connect to the local, default instance of sql server.
microsoft.sqlserver.management.common.serverconnection conn = new serverconnection(@"geovi-bd87b6b9c\geovindu", "geovindu", "888888");
server srv = new server(conn);
//reference the adventureworks2012 database.
database db = srv.databases["du"];
//define a userdefinedfunction object variable by supplying the parent database and the name arguments in the constructor.
userdefinedfunction udf = new userdefinedfunction(db, "isoweek");
//set the textmode property to false and then set the other properties.
udf.textmode = false;
udf.datatype = datatype.int;
udf.executioncontext = executioncontext.caller;
udf.functiontype = userdefinedfunctiontype.scalar;
udf.implementationtype = implementationtype.transactsql;
//add a parameter.
userdefinedfunctionparameter par = new userdefinedfunctionparameter(udf, "@date", datatype.datetime);
udf.parameters.add(par);
//set the textbody property to define the user-defined function.
udf.textbody = "begin declare @isoweek int set @isoweek= datepart(wk,@date)+1 -datepart(wk,cast(datepart(yy,@date) as char(4))+'0104') if (@isoweek=0) set @isoweek=dbo.isoweek(cast(datepart(yy,@date)-1 as char(4))+'12'+ cast(24+datepart(day,@date) as char(2)))+1 if ((datepart(mm,@date)=12) and ((datepart(dd,@date)-datepart(dw,@date))>= 28)) set @isoweek=1 return(@isoweek) end;";
//create the user-defined function on the instance of sql server.
udf.create();
//remove the user-defined function.
// udf.drop();
}
/// <summary>
/// 涂聚文 2017-06-02
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_click(object sender, eventargs e)
{
try
{
//涂聚文 2017-06-02
microsoft.sqlserver.management.common.serverconnection serverconn = new serverconnection(@"geovi-bd87b6b9c\geovindu", "geovindu", "888888");
string sqlconnectionstring = @"data source=geovi-bd87b6b9c\geovindu;initial catalog=du;user id=geovin du;password=888888";
//1.有报错问题
//fileinfo file = new fileinfo("fu.sql");
//string script = file.opentext().readtoend();
//script = script.replace("\t", " ").replace("\n", " ");
//sqlconnection conn = new sqlconnection(sqlconnectionstring);
//server server = new server(serverconn);//new serverconnection(conn)
//database db = server.databases["du"];
//server.connectioncontext.executenonquery(script);//出问题
sqlconnection conn = new sqlconnection(sqlconnectionstring);
conn.open();
string script = file.readalltext("fu.sql");
// split script on go command
ienumerable<string> commandstrings = regex.split(script, @"^\s*go\s*$", regexoptions.multiline | regexoptions.ignorecase);
foreach (string commandstring in commandstrings)
{
if (commandstring.trim() != "")
{
new sqlcommand(commandstring, conn).executenonquery();
}
}
messagebox.show("database updated successfully.");
}
catch(exception ex)
{
messagebox.show(ex.message.tostring());
}
}
/// <summary>
/// run an .sql script trough sqlcmd.
/// </summary>
/// <param name="filename">the .sql script</param>
/// <param name="machinename">the name of the server.</param>
/// <param name="databasename">the name of the database to connect to.</param>
/// <param name="trustedconnection">use a trusted connection.</param>
/// <param name="args">the arguments passed to the sql script.</param>
public void runsqlscript(string filename, string machinename, string databasename, bool trustedconnection, string[] args)
{
// simple checks
if (!path.getextension(filename).equals(".sql", stringcomparison.invariantculture))
throw new exception("the file doesn't end with .sql.");
// check for used arguments
foreach (var shortarg in new[] { "s", "d", "e", "i" })
{
var tmparg = args.singleordefault(a => a.startswith(string.format("-{0}", shortarg), stringcomparison.invariantculture));
if (tmparg != null)
throw new argumentexception(string.format("cannot pass -{0} argument to sqlcmd for a second time.", shortarg));
}
// check the params for trusted connection.
var userarg = args.singleordefault(a => a.startswith("-u", stringcomparison.invariantculture));
var passwordarg = args.singleordefault(a => a.startswith("-p", stringcomparison.invariantculture));
if (trustedconnection)
{
if (userarg != null)
throw new argumentexception("cannot pass -h argument when trustedconnection is used.");
if (passwordarg != null)
throw new argumentexception("cannot pass -p argument when trustedconnection is used.");
}
else
{
if (userarg == null)
throw new argumentexception("exspecting username(-h) argument when trustedconnection is not used.");
if (passwordarg == null)
throw new argumentexception("exspecting password(-p) argument when trustedconnection is not used.");
}
// set the working directory. (can be needed with ouputfile)
// todo: test if the above statement is correct
var tmpdirectory = directory.getcurrentdirectory();
var directory = path.ispathrooted(filename) ? path.getdirectoryname(filename) : path.combine(filename);//this.projectroot
var file = path.getfilename(filename);
directory.setcurrentdirectory(directory);
// create cmd line
var cmd = string.format(string.format("sqlcmd -s {0} -d {1} -i \"{2}\"", machinename, databasename, file));
foreach (var argument in args.where(a => a.startswith("-", stringcomparison.invariantcultureignorecase)))
cmd += " " + argument;
if (trustedconnection)
cmd += " -e";
// create the process
var process = new system.diagnostics.process();
process.startinfo.filename = "cmd";
process.startinfo.createnowindow = true;
process.startinfo.useshellexecute = false;
process.startinfo.redirectstandardoutput = true;
process.startinfo.redirectstandardinput = true;
// start the application
process.start();
process.standardinput.writeline("@echo off");
process.standardinput.writeline(string.format("cd {0}", directory));
process.standardinput.writeline(cmd);
process.standardinput.writeline("exit");
process.standardinput.flush();
process.waitforexit();
// write the output to my debug folder and restore the current directory
// debug.write(process.standardoutput.readtoend());
directory.setcurrentdirectory(tmpdirectory);
}
// public void restore(odbcconnection sqlcon, string databasefullpath, string backuppath)
// {
// using (sqlcon)
// {
// string usemaster = "use master";
// odbccommand usemastercommand = new odbccommand(usemaster, sqlcon);
// usemastercommand.executenonquery();
// // the below query will rollback any transaction which is running on that database and brings sql server database in a single user mode.
// string alter1 = @"alter database
// [" + databasefullpath + "] set single_user with rollback immediate";
// odbccommand alter1cmd = new odbccommand(alter1, sqlcon);
// alter1cmd.executenonquery();
// // the below query will restore database file from disk where backup was taken ....
// string restore = @"restore database
// [" + databasefullpath + "] from disk = n'" +
// backuppath + @"' with file = 1, nounload, stats = 10";
// odbccommand restorecmd = new odbccommand(restore, sqlcon);
// restorecmd.executenonquery();
// // the below query change the database back to multiuser
// string alter2 = @"alter database
// [" + databasefullpath + "] set multi_user";
// odbccommand alter2cmd = new odbccommand(alter2, sqlcon);
// alter2cmd.executenonquery();
// cursor.current = cursors.default;
// }
// }
vs 2010 报错：
+ $exception {"混合模式程序集是针对“v2.0.50727”版的运行时生成的，在没有配置其他信息的情况下，无法在 4.0 运行时中加载该程序集。":null} system.exception {system.io.fileloadexception}
app.config 配置：
1.一种方式
<startup uselegacyv2runtimeactivationpolicy="true">
<supportedruntime version="v4.0" sku=".netframework,version=v4.0"/>
<supportedruntime version="v2.0.50727"/>
</startup>
2.二种方式
<startup uselegacyv2runtimeactivationpolicy="true">
<supportedruntime version="v4.0"/>
</startup>
以上就是关于csharp的实例教程的详细内容。
