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 }