public static DataTable fill(SqlCommand command, DataGridView dataGridView) { SqlConnection conn = Connection.getConnection(); command.Connection = conn; command.CommandType = CommandType.StoredProcedure; DataTable dataTable = new DataTable(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command); sqlDataAdapter.Fill(dataTable); dataGridView.DataSource = dataTable; dataGridView.RowHeadersVisible = false; dataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; dataGridView.AllowUserToAddRows = false; foreach (DataGridViewColumn column in dataGridView.Columns) { if (!column.Name.Equals("checkbox") && !column.Name.Equals("chk")) { column.ReadOnly = true; } } Connection.close(conn); return(dataTable); }
public static int execute(SqlCommand command, String actionDescription, Boolean showMessage) { int affectedRows = -1; int identity = -1; SqlConnection conn = Connection.getConnection(); command.Connection = conn; command.CommandType = CommandType.StoredProcedure; affectedRows = command.ExecuteNonQuery(); if (affectedRows > 0) { string sqlIdentity = "SELECT @@IDENTITY"; using (SqlCommand cmdIdentity = new SqlCommand(sqlIdentity, conn)) { if (!(cmdIdentity.ExecuteScalar() is DBNull)) { identity = Convert.ToInt32(cmdIdentity.ExecuteScalar()); } else { identity = 1; } } } if (showMessage) { MessageBox.Show(actionDescription + " realizada satisfactoriamente", actionDescription, MessageBoxButtons.OK, MessageBoxIcon.Information); } Connection.close(conn); return(identity); }
public static SortedList <int, OpcionMenu> getOptionMenu(int idRol) { SortedList <int, OpcionMenu> menuOptions = new SortedList <int, OpcionMenu>(); SqlConnection conn = Connection.getConnection(); string storedProcedureName = "PUNTO_ZIP.sp_menu_list_functionality_by_user"; SqlCommand command = new SqlCommand(storedProcedureName); command.Connection = conn; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@p_id_rol", idRol); SqlDataReader reader = command.ExecuteReader() as SqlDataReader; int position = 0; if (reader.HasRows) { while (reader.Read()) { OpcionMenu menuOption = new OpcionMenu(); menuOption.descripcion = reader["Descripcion"].ToString(); menuOption.idFuncionalidad = Convert.ToInt32(reader["Id_Funcionalidad"]); menuOptions.Add(position, menuOption); position++; } } Connection.close(conn); return(menuOptions); }
public static void fillCheckedListBox(CheckedListBox list, String dataSource, String valueMember, String displayMember, String whereMember, String orderMember) { SqlConnection conn = Connection.getConnection(); DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter(String.Format("SELECT {0} AS 'Value',{1} AS 'Display' FROM {2} {3} {4}", valueMember, displayMember, dataSource, String.IsNullOrEmpty(whereMember) ? "" : "WHERE " + whereMember, String.IsNullOrEmpty(orderMember) ? "" : "ORDER BY " + orderMember), conn); dataAdapter.Fill(dataSet, dataSource); list.DataSource = dataSet.Tables[0].DefaultView; list.ValueMember = "Value"; list.DisplayMember = "Display"; Connection.close(conn); }
public static void fillFromProcedure(ComboBox comboBox, String dataSource, String valueMember, String displayMember, String whereMember, String orderMember) { SqlConnection conn = Connection.getConnection(); DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter("EXEC " + dataSource, conn); dataAdapter.Fill(dataSet, dataSource); DataRow row = dataSet.Tables[0].NewRow(); dataSet.Tables[0].Rows.InsertAt(row, 0); comboBox.DataSource = dataSet.Tables[0].DefaultView; comboBox.ValueMember = valueMember; comboBox.DisplayMember = displayMember; comboBox.DropDownStyle = ComboBoxStyle.DropDownList; comboBox.Text = "(Seleccione una Opcion)"; Connection.close(conn); }
public static void fill(ComboBox comboBox, String dataSource, String valueMember, String displayMember, String whereMember, String orderMember) { SqlConnection conn = Connection.getConnection(); DataSet dataSet = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter(String.Format("SELECT {0} AS 'Value',{1} AS 'Display' FROM {2} {3} {4}", valueMember, displayMember, dataSource, String.IsNullOrEmpty(whereMember) ? "" : "WHERE " + whereMember, String.IsNullOrEmpty(orderMember) ? "" : "ORDER BY " + orderMember), conn); dataAdapter.Fill(dataSet, dataSource); DataRow row = dataSet.Tables[0].NewRow(); dataSet.Tables[0].Rows.InsertAt(row, 0); comboBox.DataSource = dataSet.Tables[0].DefaultView; comboBox.ValueMember = "Value"; comboBox.DisplayMember = "Display"; comboBox.DropDownStyle = ComboBoxStyle.DropDownList; comboBox.Text = "(Seleccione una Opcion)"; Connection.close(conn); }
public static object execute(SqlCommand command) { object result = null; try { SqlConnection conn = Connection.getConnection(); command.Connection = conn; result = command.ExecuteScalar(); Connection.close(conn); } catch (SqlException sqlEx) { string errorMessage = sqlEx.Message; MessageBox.Show(errorMessage, "Ejecucion de Procedure", MessageBoxButtons.OK, MessageBoxIcon.Error); } catch (Exception ex) { string errorMessage = ex.Message; MessageBox.Show(errorMessage, "Ejecucion de Procedure", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(result); }