仿ISQL功能的实现，可以实现批处理功能
具体请见下载文件：/Files/bigmouthz/DNet写的数据库isql执行程序（含源码）.rar
部分代码如下：
DBCore.DataBaseVisitor.AbsDBHelper dbhelper = null ;
private void bt_DBLink_Click(object sender, System.EventArgs e)
{
try
{
this.GetDBConfig(xml);
this.GetControlValue();
dbhelper = GetDBHelper();
System.Windows.Forms.MessageBox.Show("数据库连接正常！");
this.rtb_State.Text += "数据库连接正常！ :_)\r\n";
}
catch
{
System.Windows.Forms.MessageBox.Show("数据库连接失败！");
this.rtb_State.Text += "数据库连接失败！ :_（\r\n";
}
}
private void bt_LoadDBConfig_Click(object sender, System.EventArgs e)
{
this.GetDBConfig(xml);
this.SetControlValue();
this.rtb_State.Text += "数据库配置载入完成！ :_)\r\n";
}
private void bt_SaveDBConfig_Click(object sender, System.EventArgs e)
{
this.GetControlValue();
this.SetDBConfig(xml);
this.rtb_State.Text += "文件已经保存完成！ :_)\r\n";
}
private void bt_TestDBLink_Click(object sender, System.EventArgs e)
{
try
{
this.GetDBConfig(xml);
this.GetControlValue();
DBCore.DataBaseVisitor.AbsDBHelper dbhelper = GetDBHelper();
object o = dbhelper.ExecuteScalar(TestSql);
this.rtb_State.Text = "测试时间：" + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+ "\r\n";
this.rtb_State.Text += o == null ? "None Data!":o.ToString();
this.rtb_State.Text += "执行完成！ :_)\r\n";
this.rtb_sql.Text = TestSql;
System.Windows.Forms.MessageBox.Show("测试通过！数据库连接正常！");
}
catch
{
System.Windows.Forms.MessageBox.Show("数据库连接测试失败！");
this.rtb_State.Text += "数据库连接测试失败！ :_)\r\n";
}
}
private void bt_load_Click(object sender, System.EventArgs e)
{
System.Windows.Forms.OpenFileDialog OFD;
OFD = new System.Windows.Forms.OpenFileDialog();
if(OFD.ShowDialog() == DialogResult.OK)
{
System.IO.StreamReader sr = new System.IO.StreamReader(OFD.FileName,System.Text.Encoding.GetEncoding("gb2312"));
this.rtb_sql.Text = sr.ReadToEnd();
sr.Close();
this.rtb_State.Text += "载入文件完成！ :_)\r\n";
}
}
private void batch_Exec()
{
string split = "\n";
string tmp = this.rtb_sql.Text;
int ibit = 0;
int itmp = 0;
while (ibit >= 0)
{
ibit = tmp.IndexOf(split,itmp);
if (ibit - itmp < 0) break;
string line = tmp.Substring(itmp, ibit - itmp);
itmp = ibit + 1;
if (line == null || line == "") continue;
auto_Exec(line);
}
}
private void auto_Exec(string filepath)
{
try
{
System.IO.StreamReader sr = new System.IO.StreamReader(filepath,System.Text.Encoding.GetEncoding("gb2312"));
this.rtb_sql.Text = sr.ReadToEnd();
sr.Close();
this.rtb_State.Text += "自动载入文件完成！ :_)\r\n";
Singe_Exec();
}
catch
{
this.rtb_sql.Text = "";
this.rtb_State.Text += "自动载入文件失败！ :_(\r\n";
}
}
private void Singe_Exec()
{
if (dbhelper == null)
{
try
{
this.GetDBConfig(xml);
this.GetControlValue();
dbhelper = GetDBHelper();
this.rtb_State.Text += "数据库连接成功！ :_)\r\n";
}
catch
{
System.Windows.Forms.MessageBox.Show("数据库连接失败！");
this.rtb_State.Text += "数据库连接失败！ :_(\r\n";
}
}
try
{
string sql = this.rtb_sql.Text;
sql = sql.Replace("\n"," ").Replace("\r"," ").Replace("\t"," ");
if (this.cb_resulttype.Checked)
{
System.Data.DataSet ds = dbhelper.ExecuteDataset(sql);
this.dg_display.DataSource = ds;
this.dg_display.Expand(-1);
this.rtb_State.Text += "执行时间：" + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+ "\r\n";
this.rtb_State.Text += "执行完成！ :_)\r\n";
}
else
{
int o = dbhelper.ExecuteNonQuery(sql);
this.dg_display.DataSource = null;
this.rtb_State.Text += "执行时间：" + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+ "\r\n";
this.rtb_State.Text += "执行影响行数:" + o.ToString() +"\r\n";
this.rtb_State.Text += "执行完成！ :_)\r\n";
}
}
catch
{
System.Windows.Forms.MessageBox.Show("执行语句失败！:-(");
this.rtb_State.Text += "执行语句失败！ :_(\r\n";
}
}
private void bt_Exec_Click(object sender, System.EventArgs e)
{
if (this.cb_ExecType.Checked)
{
batch_Exec();
}
else
{
Singe_Exec();
}
}
#region Control_DBConfigVar
private void SetControlValue()
{
this.cb_dblink.Text = this.DBConnection;
this.tb_UserID.Text = this.UserID;
this.tb_PassWord.Text = this.Password;
this.tb_DBAddress.Text = this.DataSource;
this.tb_DBName.Text = this.InitialCatalog;
}
private void GetControlValue()
{
this.DBConnection = this.cb_dblink.Text;
this.UserID = this.tb_UserID.Text;
this.Password = this.tb_PassWord.Text;
this.DataSource = this.tb_DBAddress.Text;
this.InitialCatalog = this.tb_DBName.Text;
}
#endregion
#region DBConfig
private string xml = System.Environment.CurrentDirectory + "/DBC.dll";
private string Sql="user id=$0$;password=$1$;data source=$2$;initial catalog=$3$;persist security info=False";//Sql专用引擎连接串
private string OleDb="Provider=sqloledb;Data Source=$2$;Initial Catalog=$3$;User Id=$0$;Password=$1$;";//OleDb引擎连接串
private string Odbc="Driver={SQL Server};Server=$2$;Database=$3$;Uid=$0$;Pwd=$1$;";//Odbc引擎连接串
private string DBConnection;//选择连接的方式 Sql|Odbc|OleDb
private string UserID;//数据库用户ID 在升级数据库中要求使用SA用户
private string Password;//数据库连接口令
private string DataSource;//数据库服务器的地址或者数据库服务器名称
private string InitialCatalog;//指定要访问的数据库名称
private string TestSql = "select * from dtproperties";//测试Sql
private void SetDBConfig(string xml)
{
/* *
<?xml version="1.0" encoding="gb2312"?>
<DBConfig>
<DataBase>
<!--这个区域禁止修改Begin-->
<Sql>user id=$0$;password=$1$;data source=$2$;initial catalog=$3$;persist security info=False</Sql>
<Odbc>Driver={SQL Server};Server=$2$;Database=$3$;Uid=$0$;Pwd=$1$;</Odbc>
<OleDb>Provider=sqloledb;Data Source=$2$;Initial Catalog=$3$;User Id=$0$;Password=$1$;</OleDb>
<!--这个区域禁止修改End-->
<DBConnection>Sql</DBConnection> <!--选择连接的方式 Sql|Odbc|OleDb -->
<UserID>Sa</UserID> <!--数据库用户ID 在升级数据库中要求使用SA用户-->
<Password>jsl</Password> <!--数据库连接口令-->
<DataSource>10.200.1.251</DataSource> <!--数据库服务器的地址或者数据库服务器名称-->
<InitialCatalog>Qinghai</InitialCatalog><!--指定要访问的数据库名称-->
</DataBase>
</DBConfig>
<!--FrameWork DBConfig.XML-->
* */
XmlTextWriter writer = new XmlTextWriter(xml,System.Text.Encoding.UTF8);
writer.Formatting = System.Xml.Formatting.Indented;
writer.IndentChar = '\t';
writer.WriteStartDocument();
writer.WriteComment("DataBase Config." + System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
writer.WriteStartElement("DBConfig");
writer.WriteStartElement("DataBase");
writer.WriteComment("这个区域禁止修改Begin");
writer.WriteElementString("Sql","user id=$0$;password=$1$;data source=$2$;initial catalog=$3$;persist security info=False");
writer.WriteElementString("OleDb","Provider=sqloledb;Data Source=$2$;Initial Catalog=$3$;User Id=$0$;Password=$1$;");
writer.WriteElementString("Odbc","Driver={SQL Server};Server=$2$;Database=$3$;Uid=$0$;Pwd=$1$;");
writer.WriteComment("这个区域禁止修改End");
writer.WriteElementString("DBConnection",DBConnection);
writer.WriteComment("选择连接的方式 Sql|Odbc|OleDb");
writer.WriteElementString("UserID",UserID);
writer.WriteComment("数据库用户ID 在升级数据库中要求使用SA用户");
writer.WriteElementString("Password",Password);
writer.WriteComment("数据库连接口令");
writer.WriteElementString("DataSource",DataSource);
writer.WriteComment("数据库服务器的地址或者数据库服务器名称");
writer.WriteElementString("InitialCatalog",InitialCatalog);
writer.WriteComment("指定要访问的数据库名称");
writer.WriteElementString("TestSql","select * from dtproperties");
writer.WriteComment("测试用的Sql");
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.WriteComment("DBCore DBConfig.XML");
writer.Flush();
writer.Close();
}
private void GetDBConfig(string xml)
{
XmlTextReader reader = null;
try
{
reader = new XmlTextReader(xml);
while(!reader.EOF)
{
while(reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
switch(reader.Name)
{
case "Sql":
Sql = reader.ReadElementString("Sql"); break;
case "OleDb":
OleDb = reader.ReadElementString("OleDb"); break;
case "Odbc":
Odbc = reader.ReadElementString("Odbc"); break;
case "DBConnection":
DBConnection = reader.ReadElementString("DBConnection"); break;
case "UserID":
UserID = reader.ReadElementString("UserID"); break;
case "Password":
Password = reader.ReadElementString("Password"); break;
case "DataSource":
DataSource = reader.ReadElementString("DataSource"); break;
case "InitialCatalog":
InitialCatalog = reader.ReadElementString("InitialCatalog"); break;
case "TestSql":
TestSql = reader.ReadElementString("TestSql"); break;
}
}
else if (reader.NodeType == XmlNodeType.EndElement)
{
break;
}
}
}
}
finally
{
if (reader != null) reader.Close();
}
}
private DBCore.DataBaseVisitor.AbsDBHelper GetDBHelper()
{
DBCore.DataBaseVisitor.AbsDBHelper dbhelper ;
switch(DBConnection)
{
case "Sql":
dbhelper = new DBCore.DataBaseVisitor.JSLSqlHelper();
Sql = Sql.Replace("$0$",UserID);
Sql = Sql.Replace("$1$",Password);
Sql = Sql.Replace("$2$",DataSource);
Sql = Sql.Replace("$3$",InitialCatalog);
dbhelper.SetDBConnection = Sql;
break;
case "OleDb":
dbhelper = new DBCore.DataBaseVisitor.JSLOleDbHelper();
OleDb = OleDb.Replace("$0$",UserID);
OleDb = OleDb.Replace("$1$",Password);
OleDb = OleDb.Replace("$2$",DataSource);
OleDb = OleDb.Replace("$3$",InitialCatalog);
dbhelper.SetDBConnection = OleDb;
break;
case "Odbc":
dbhelper = new DBCore.DataBaseVisitor.JSLOdbcHelper();
Odbc = Odbc.Replace("$0$",UserID);
Odbc = Odbc.Replace("$1$",Password);
Odbc = Odbc.Replace("$2$",DataSource);
Odbc = Odbc.Replace("$3$",InitialCatalog);
dbhelper.SetDBConnection = Odbc;
break;
default :
dbhelper = null;
break;
}
return dbhelper;
}
#endregion
private void bt_SaveFile_Click(object sender, System.EventArgs e)
{
System.Windows.Forms.SaveFileDialog SFD;
SFD = new System.Windows.Forms.SaveFileDialog();
SFD.Filter = "txt files (*.txt)|*.txt|sql Files(*.sql)|*.sql|All files (*.*)|*.*";
SFD.Title = "Save an Sql/Text File";
SFD.RestoreDirectory = true ;
if (SFD.ShowDialog() == DialogResult.OK)
{
if(SFD.FileName != "")
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(SFD.OpenFile(),System.Text.Encoding.GetEncoding("gb2312")))
{
sw.Write(this.rtb_sql.Text);
sw.Close();
}
}
}
}
}
