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);
}