/// <summary>
/// 根据传入的sql语句返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int exec_sql(string sql)
{
int result = 0;
SqlConnection conn = new SqlConnection(textBoxConnectionString.Text);
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.CommandTimeout = 60 * 10;//10分钟
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
return(result);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 返回第一列第一行
/// </summary>
/// <param name="sql">查询sql</param>
/// <returns>字符串</returns>
public string ExecuteScalar(string sql)
{
string result = "";
SqlConnection conn = new SqlConnection(textBoxConnectionString.Text);
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
result = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
return(result);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 判断是否可以连接数据库
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static bool getConnectState()
{
bool isConnected = false;
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
try
{
//conn.ConnectionTimeout = 1000;
conn.Open();
return(true);
}
catch (Exception ex)
{
CSHelper.saveErrLog(ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
return(false);
}
finally
{
conn.Close();
conn.Dispose();
}
return(isConnected);
}
public static List <Dictionary <string, object> > GetListBySql(string sql)
{
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
List <Dictionary <string, object> > list = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > result;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Dictionary <string, object> dict = new Dictionary <string, object>();
for (int i = 0; i < dr.FieldCount; i++)
{
dict.Add(dr.GetName(i), dr.GetValue(i));
}
list.Add(dict);
}
result = list;
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
result = null;
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 返回第一列第一行
/// </summary>
/// <param name="sql">查询sql</param>
/// <returns>字符串</returns>
public static string ExecuteScalar(string sql)
{
string result = string.Empty;
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
var obj = cmd.ExecuteScalar();
if (obj == DBNull.Value)
{
return(result);
}
else if (obj == null)
{
return(result);
}
result = obj.ToString();
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
return(result);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 判断存在
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ifExist(string sql)
{
int result = 0;
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
result = int.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
return(result);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 查询数据库文件大小
/// </summary>
/// <param name="dbname"></param>
public void countDatabaseSize(string dbname)
{
//SqlServer是以8k为一页
var sql = "select name, convert(float,size) * (8192.0/1024.0)/1024. size from " + dbname + ".dbo.sysfiles";
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ApendLog("文件名:" + dr.GetValue(0).ToString() + " 大小:" + dr.GetValue(1).ToString() + "M");
}
}
catch (Exception ex)
{
CSHelper.saveErrLog(sql + ex.Message, DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
}
finally
{
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 使用事务执行多条SQL(插入、更新、删除)
/// </summary>
/// <param name="sqls"></param>
public static void Exec_Tansaction(List <string> sqls)
{
var sqlsb = new StringBuilder();;
if (sqls.Count == 0)
{
return;
}
SqlConnection conn = new SqlConnection(sqlconn);
conn.Open();
// 启动一个事务。
SqlTransaction myTran = conn.BeginTransaction();
// 为事务创建一个命令
SqlCommand myCom = new SqlCommand();
myCom.Connection = conn;
myCom.Transaction = myTran;
try
{
foreach (string sql in sqls)
{
sqlsb.AppendLine(sql);
myCom.CommandText = sql;
myCom.ExecuteNonQuery();
}
myTran.Commit();//提交事务
}
catch (Exception ex)
{
myTran.Rollback();
CSHelper.saveErrLog("提交数据失败" + ex.Message + "\n\r" + sqlsb.ToString(), DateTime.Now.ToString("yyyy-MM-dd") + "-sql_err");
//返回异常的错误信息
//MessageBox.Show("提交数据失败!\n" + Ex.ToString(), "异常信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
throw new Exception("提交数据失败!\n" + ex.ToString());
}
finally
{
conn.Close();
}
}
public void insertToDB(StringBuilder sb_sqls, string connStr = null)
{
SqlConnection conn;
if (string.IsNullOrEmpty(connStr))
{
conn = new SqlConnection(textBoxConnectionString.Text);
}
else
{
conn = new SqlConnection(connStr);
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
cmd.CommandText = sb_sqls.ToString();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
richTextBox1.AppendText(ex.Message + "\n\r" + ex.StackTrace);
tran.Rollback();
CSHelper.saveErrLog(sb_sqls.ToString(), "批量执行失败的sql");
}
finally
{
conn.Close();
tran.Dispose();
conn.Dispose();
}
}
//生成文档
public void bulidMarkdown()
{
//显示加载
ShowLoading(true);
try
{
string sql = "";
sql += @"SELECT d.name
表名 ,f.remark 表备注,
a.colorder 字段序号 ,
a.name 字段名 ,
( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END ) 标识 ,
( CASE WHEN ( SELECT COUNT(*)
FROM sysobjects
WHERE ( NAME IN (
SELECT NAME
FROM sysindexes
WHERE ( id = a.id )
AND ( indid IN (
SELECT indid
FROM sysindexkeys
WHERE ( id = a.id )
AND ( colid IN (
SELECT
colid
FROM
syscolumns
WHERE
( id = a.id )
AND ( NAME = a.name ) ) ) ) ) ) )
AND ( xtype = 'PK' )
) > 0 THEN '√'
ELSE ''
END ) 主键 ,
b.name 类型 ,
a.length 占用字节数 ,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数 ,
( CASE WHEN a.isnullable = 1 THEN '√'
ELSE ''
END ) 允许空 ,
ISNULL(e.text, '') 默认值,
CASE WHEN (SELECT g.remark FROM t_table_field g WHERE g.table_name=d.NAME AND g.table_field=a.name) IS NOT NULL THEN (SELECT g.remark FROM t_table_field g WHERE g.table_name=d.NAME AND g.table_field=a.name) ELSE (SELECT pbc_cmnt FROM dbo.pbcatcol WHERE pbc_tnam=d.name AND pbc_cnam=a.name) END 备注
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN t_table_name f ON d.NAME=f.table_name";
if (rtnCurrentTable.Checked == true)
{
sql += " WHERE d.name = '" + label4.Text.Trim() + "'";
}
else
{
//sql += " WHERE d.name IN(SELECT DISTINCT pbc_tnam FROM pbcatcol )";
sql += " WHERE d.name IN(" + textBox4.Text.Trim() + ")";
}
if (rbtAllTable.Checked)
{
sql += " ORDER BY PATINDEX('% ' + CONVERT(nvarchar(4000), d.name) + ' %', ' ' + CONVERT(nvarchar(4000), Replace('" + textBox4.Text.Trim().Replace("'", "") + "', ',',' , ')) + ' ')";
}
else
{
sql += @"ORDER BY a.id ,a.colorder";
}
SqlConnection conn = new SqlConnection(CSHelper.sqlconn);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
if (!chkXML.Checked && rbtAllTable.Checked)
{
sb.AppendLine("[toc]");
sb.AppendLine("##新产品后台数据库结构");
}
else if (chkXML.Checked)
{
sb.AppendLine(Properties.Resources.headtext);
}
int i = 0;
string lastTableName = "";
// var iflastflied = false;
while (dr.Read())
{
//每个表的头部内容
if (i == 0 || lastTableName != dr["表名"].ToString())
{
if (!chkXML.Checked)
{
sb.AppendLine("\n");
sb.AppendLine("###" + dr["表备注"] + " " + dr["表名"]);
sb.AppendLine("|字段|类型|主键|标识|允许空|默认值|备注|");
sb.AppendLine("| :-------- | --------:| :--: | :--: | :--: | :--: | :--: |");
}
else
{
if (i != 0)
{
sb.AppendLine(" </UML:Classifier.feature>");
sb.AppendLine("</UML:Class>");
}
sb.AppendLine("\n");
sb.AppendLine(" <UML:Class name=\"" + dr["表名"] + " " + dr["表备注"] + "\" isLeaf=\"false\" xmi.id=\"{" + Guid.NewGuid() + "}\" isAbstract=\"false\" visibility=\"public\">");
sb.AppendLine(" <UML:ModelElement.taggedValue>");
sb.AppendLine(" <UML:TaggedValue tag=\"new\" value=\"false\"/>");
sb.AppendLine(" <UML:TaggedValue tag=\"unsafe\" value=\"false\"/>");
sb.AppendLine(" </UML:ModelElement.taggedValue>");
sb.AppendLine(" <UML:Classifier.feature>");
}
}
//每个表的中间内容
if (chkIgnoreNull.Checked)
{
if (dr["备注"] != DBNull.Value)
//继续执行
{
if (dr["备注"].ToString().Trim() != "")
{
sb.AppendLine("|" + dr["字段名"] + "|" + dr["类型"] + "(" + dr["长度"] + ")" + "|" + dr["主键"] + "|" + dr["标识"] + "|" + dr["允许空"] + "|" + FilterText(dr["默认值"]) + "|" + FilterText(dr) + "|");
}
}
}
else if (!chkXML.Checked)
{
sb.AppendLine("|" + dr["字段名"] + "|" + dr["类型"] + "(" + dr["长度"] + ")" + "|" + dr["主键"] + "|" + dr["标识"] + "|" + dr["允许空"] + "|" + FilterText(dr["默认值"]) + "|" + FilterText(dr) + "|");
}
else
{
sb.AppendLine(" <UML:Attribute name=\"" + dr["字段名"] + "\" xmi.id=\"{" + Guid.NewGuid() + "}\" ownerScope=\"instance\" visibility=\"private\" changeability=\"changeable\"> ");
sb.AppendLine(" <UML:Attribute.initialValue> ");
sb.AppendLine(" <UML:Expression body=\"" + FilterText(dr) + "\" xmi.id=\"Expr" + i + "\"/> ");
sb.AppendLine(" </UML:Attribute.initialValue> ");
sb.AppendLine(" <UML:StructuralFeature.type> ");
sb.AppendLine(" <UML:Classifier xmi.idref=\"Dttp0\"/> ");
sb.AppendLine(" </UML:StructuralFeature.type> ");
sb.AppendLine(" </UML:Attribute>");
}
//每个表的尾部内容
//var currentTableName = dr["表名"].ToString();
//if (currentTableName != lastTableName)
//{
// sb.AppendLine(" </UML:Classifier.feature>");
// sb.AppendLine("</UML:Class>");
//}
lastTableName = dr["表名"].ToString();
i++;
}
if (chkXML.Checked)
{
sb.AppendLine(" </UML:Classifier.feature>");
sb.AppendLine("</UML:Class>");
sb.AppendLine(Properties.Resources.bottomtext);
}
var fileName = "导出.md";
if (chkXML.Checked)
{
fileName = "导出.xml";
}
using (System.IO.FileStream fs = new System.IO.FileStream(Application.StartupPath + "\\" + fileName, System.IO.FileMode.Create,
System.IO.FileAccess.ReadWrite))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, Encoding.UTF8))
{
sw.WriteLine(sb.ToString());
}
}
sb.Length = 0;
}
catch (Exception ex)
{
CSHelper.saveErrLog(ex.Message + "\n" + ex.StackTrace, DateTime.Now.ToString("yyyy-MM-dd") + "-ex");
}
finally
{
//完成后自动打开生成的脚本文件夹
if (Directory.Exists(Application.StartupPath + "\\") != false)
{
string path = Application.StartupPath + "\\"; System.Diagnostics.Process.Start("explorer.exe", path);
}
//隐藏加载中图片
ShowLoading(false);
}
}