private void btnGenInsertSql_Click(object sender, EventArgs e)
{
try
{
var helper = new DbHelper(DBGlobalService.Connection);
var sql = this.textEditorControl1.Text.Trim();
var ds = helper.ExecuteDataSet(sql);
var insertSql = "insert into " + ds.Tables[0].TableName;
insertSql += "(";
foreach (DataColumn column in ds.Tables[0].Columns)
{
insertSql += column.ColumnName;
}
insertSql += ")";
foreach (var row in ds.Tables[0].Rows)
{
}
helper.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Table_Load(object sender, EventArgs e)
{
DbHelper db = new DbHelper(cnnstring);
checkedListBox1.DataSource = db.ExecuteDataSet("select * from sys.tables").Tables[0];
checkedListBox1.ValueMember = "name";
checkedListBox1.DisplayMember = "name";
}
private void btnGenerateMethod_Click(object sender, EventArgs e)
{
if (tabControl1.SelectedTab.Name == "tpSql")
{
// sql语句
Regex rg = new Regex("@[a-zA-z0-9_]+");
MatchCollection mc = rg.Matches(textBoxSql.Text.Trim());
List<string> listed_params = new List<string>();
clear_dataset();
DataTable dt = params_set.Tables["params"];
foreach (Match m in mc)
{
if (!listed_params.Contains(m.Value))
{
DataRow dr = dt.NewRow();
string match_param = m.Value;
dr[0] = match_param;
dr[1] = "string";
dt.Rows.Add(dr);
listed_params.Add(match_param);
}
}
FormMethod fm = new FormMethod();
fm.ParentFormTb = this;
fm.Type = "method";
fm.DataSource = params_set;
fm.ShowDialog();
}
else if (tabControl1.SelectedTab.Name == "tpPdr")
{
string procedure_name = textBoxPdr.Text.Trim();
// 存储过程
DbHelper db = new DbHelper(cnnstring);
string Query = @"SELECT
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=
@procedurename and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC";
db.AddParameter("@procedurename", procedure_name);
DataTable dtparams = db.ExecuteDataSet(Query, CommandType.Text).Tables[0];
List<string> listed_params = new List<string>();
clear_dataset();
DataTable dt = params_set.Tables["params"];
foreach (DataRow drp in dtparams.Rows)
{
if (!listed_params.Contains(drp["name"].ToString()))
{
DataRow dr = dt.NewRow();
dr[0] = drp["name"].ToString();
dr[1] = data_type_convert(drp["systemtype"].ToString());
dt.Rows.Add(dr);
listed_params.Add(drp["name"].ToString());
}
}
FormMethod fm = new FormMethod();
fm.ParentFormTb = this;
fm.Type = "procedure";
fm.DataSource = params_set;
fm.ShowDialog();
}
}
private string a_class(string tablename, bool is_class)
{
try
{
/*
*
* 获取数据库中该表的信息
*
*/
DbHelper db = new DbHelper(cnnstring);
string Query = @"SELECT syscolumns.name AS ColumnName,
systypes.name AS DataType,
syscolumns.length AS ColumnLength,
syscolumns.prec AS ColumnPrecision,
IsPrimaryKey = CASE
WHEN EXISTS (
SELECT 1
FROM sysobjects
INNER JOIN sysindexes
ON sysindexes.name = sysobjects.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid =
sysindexkeys.indid
WHERE xtype = 'PK'
AND parent_obj = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
) THEN 1
ELSE 0
END,
syscolumns.isnullable AS IsNullable,
sys.extended_properties.value AS ColumnDiscription
--IsIdentity = CASE syscolumns.status
-- WHEN 128 THEN 1
-- ELSE 0
-- END
FROM syscolumns
INNER JOIN systypes
ON (
syscolumns.xtype = systypes.xtype
AND systypes.name <> '_default_'
AND systypes.name <> 'sysname'
)
LEFT OUTER JOIN sys.extended_properties
ON (
sys.extended_properties.major_id = syscolumns.id
AND minor_id = syscolumns.colid
)
WHERE syscolumns.id = (
SELECT id
FROM sysobjects
WHERE NAME = @TableName
)
ORDER BY
syscolumns.colid";
db.AddParameter("@TableName", tablename);
DataTable dt = db.ExecuteDataSet(Query, CommandType.Text).Tables[0];
/*
*
* 组合成类
*
*/
string o = string.Empty;
if (is_class)
{
write_class_head(ref o, tablename);
write_insert_method(ref o, tablename, dt, 1);
write_delete_method(ref o, tablename, dt, 1);
write_update_method(ref o, tablename, dt, 1);
write_get_method(ref o, tablename, dt, 1);
write_end(ref o);
}
else
{
write_model_head(ref o, tablename);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
append(ref o, "private " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " _" + dr["columnname"].ToString() + ";", 1);
}
append(ref o, "");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
append(ref o, "public " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " " + first_letter_uplower(dr["columnname"].ToString()), 1);
append(ref o, "{", 1);
append(ref o, "get{ return _" + dr["columnname"].ToString() + "; }", 2);
append(ref o, "set{ _" + dr["columnname"].ToString() + " = value; }", 2);
append(ref o, "}", 1);
}
write_end(ref o);
}
return o;
}
catch (Exception ex)
{
throw ex;
}
}
public void EliminarTodosLosElementos()
{
try
{
string sentenciaSQL = @"DELETE [RegistroEVAutoProcesado]";
DbHelper helper = new DbHelper(NombreCadenaConexion);
DbCommand comando = helper.GetSqlStringCommond(sentenciaSQL);
helper.ExecuteDataSet(comando);
}
catch (Exception ex)
{
throw _gestorDeError.TratarExcepcion(ex,
"Fallo al intentar eliminar todos los regitros de entidades de verificación procesados ",
"EliminarTodosLosElementos");
}
}
public void EliminarElementoPorID(int id)
{
try
{
string sentenciaSQL = @"DELETE [RegistroEVAutoProcesado]
WHERE ([RegistroEVAutoProcesado].[ID] = @ID)";
DbHelper helper = new DbHelper(NombreCadenaConexion);
DbCommand comando = helper.GetSqlStringCommond(sentenciaSQL);
helper.AddInParameter(comando, "@ID", id);
helper.ExecuteDataSet(comando);
}
catch (Exception ex)
{
throw _gestorDeError.TratarExcepcion(ex,
string.Format("Fallo al intentar eliminar un regitro de entidad de verificación procesado con Id: {0}", id),
"EliminarElementoPorID");
}
}
private void CompareViewCode(ConnectionType otherConn, List<TableType> thisObjs, List<TableType> otherObjs, StringBuilder builder)
{
try
{
var helper = new DbHelper(this.dataInfo);
var otherHelper = new DbHelper(otherConn);
var views = new List<string>();
foreach (var thisView in thisObjs)
{
if (otherObjs.FindTable(thisView.Name) != null)
{
views.Add(thisView.Name);
continue;
}
}
var v_str = "";
for (var index = 0; index < views.Count; index++)
{
if (index > 0)
v_str += ",";
v_str += string.Format("'{0}'", views[index]);
}
var sql = string.Format(@"SELECT obj.name,m.definition
FROM sys.sql_modules m JOIN sys.objects obj
ON m.object_id= obj.object_id
and obj.type='v'
and obj.name in ({0}) order by obj.name", v_str);
var thisDs = helper.ExecuteDataSet(sql);
var otherDs = otherHelper.ExecuteDataSet(sql);
for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++)
{
var thisRow = thisDs.Tables[0].Rows[index];
var otherRow = otherDs.Tables[0].Rows[index];
var thisSql = thisRow["definition"].ToString().Trim();
var otherSql = otherRow["definition"].ToString().Trim();
var thisReader = new System.IO.StringReader(thisSql);
var otherReader = new System.IO.StringReader(otherSql);
var thisFirstLine = thisReader.ReadLine().Replace("[", "").Replace("]","");
var otherFirstLine = otherReader.ReadLine().Replace("[", "").Replace("]", "");
if (thisFirstLine != otherFirstLine)
{
builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
builder.AppendLine();
continue;
}
var this_ = thisReader.ReadToEnd();
var other_ = otherReader.ReadToEnd();
if (this_ != other_)
{
builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
builder.AppendLine();
continue;
}
//if (thisRow["definition"].ToString().Trim() != otherRow["definition"].ToString().Trim())
//{
// builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
// builder.AppendLine();
//}
}
helper.Close();
otherHelper.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CompareFunctionCode(ConnectionType otherConn,StringBuilder builder)
{
var helper = new DbHelper(this.dataInfo);
var otherHelper = new DbHelper(otherConn);
var views = new List<string>();
var v_str = "";
for (var index = 0; index < views.Count; index++)
{
if (index > 0)
v_str += ",";
v_str += string.Format("'{0}'", views[index]);
}
var sql = string.Format(@"SELECT obj.type,obj.name,m.definition
FROM sys.sql_modules m JOIN sys.objects obj
ON m.object_id= obj.object_id
and obj.type in ('if','fn')
order by obj.name", v_str);
var thisDs = helper.ExecuteDataSet(sql);
var otherDs = otherHelper.ExecuteDataSet(sql);
#region 判断本地多的函数
for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++)
{
var thisRow = thisDs.Tables[0].Rows[index];
var this_type = thisRow["type"].ToString().Trim();
var this_definition = thisRow["definition"].ToString().Trim();
var this_name = thisRow["name"].ToString().Trim();
bool exsited = false;
var func_type = "";
if (this_type.Equals("if", StringComparison.OrdinalIgnoreCase))
{
func_type = "表值函数";
}
else if (this_type.Equals("fn", StringComparison.OrdinalIgnoreCase))
{
func_type = "值函数";
}
for (var j = 0; j < otherDs.Tables[0].Rows.Count; j++)
{
var otherRow = otherDs.Tables[0].Rows[j];
var other_type = otherRow["type"].ToString().Trim();
var other_definition = otherRow["definition"].ToString().Trim();
var other_name = otherRow["name"].ToString().Trim();
if (this_type == other_type && this_name == other_name)
{
exsited = true;
continue;
}
}
if (!exsited)
{
builder.AppendFormat("本地新增{0} ： {1}", func_type, this_name);
builder.AppendLine();
}
}
#endregion
#region 判断本地少的函数
for (var index = 0; index < otherDs.Tables[0].Rows.Count; index++)
{
var otherRow = otherDs.Tables[0].Rows[index];
var other_type = otherRow["type"].ToString().Trim();
var other_definition = otherRow["definition"].ToString().Trim();
var other_name = otherRow["name"].ToString().Trim();
bool exsited = false;
var func_type = "";
if (other_type.Equals("if", StringComparison.OrdinalIgnoreCase))
{
func_type = "表值函数";
}
else if (other_type.Equals("fn", StringComparison.OrdinalIgnoreCase))
{
func_type = "值函数";
}
for (var j = 0; j < thisDs.Tables[0].Rows.Count; j++)
{
var thisRow = thisDs.Tables[0].Rows[j];
var this_type = thisRow["type"].ToString().Trim();
var this_definition = thisRow["definition"].ToString().Trim();
var this_name = thisRow["name"].ToString().Trim();
if (this_type == other_type && this_name == other_name)
{
exsited = true;
continue;
}
}
if (!exsited)
{
builder.AppendFormat("本地缺少{0} ： {1}", func_type, other_type);
builder.AppendLine();
}
}
#endregion
#region 判断函数内容的不同
for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++)
{
var thisRow = thisDs.Tables[0].Rows[index];
var this_type = thisRow["type"].ToString().Trim();
var this_definition = thisRow["definition"].ToString().Trim();
var this_name = thisRow["name"].ToString().Trim();
var func_type = "";
if (this_type.Equals("if", StringComparison.OrdinalIgnoreCase))
{
func_type = "表值函数";
}
else if (this_type.Equals("fn", StringComparison.OrdinalIgnoreCase))
{
func_type = "值函数";
}
for (var j = 0; j < otherDs.Tables[0].Rows.Count; j++)
{
var otherRow = otherDs.Tables[0].Rows[j];
var other_type = otherRow["type"].ToString().Trim();
var other_definition = otherRow["definition"].ToString().Trim();
var other_name = otherRow["name"].ToString().Trim();
if (this_type == other_type && this_name == other_name)
{
if (this_definition != other_definition)
{
builder.AppendFormat("{0} 内容不同： {1}", func_type, this_name);
builder.AppendLine();
}
continue;
}
}
}
#endregion
}
