How to create SQL Server Login programmatically?

Q. How to create SQL Server Login programmatically?

I got this question from Daniweb C# forum, and of course when I need to do something against SQL Server object from .net I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scripts then embedding them into stored procedure then call it…. off…

Anyway to avoid showing how much I’m talkative…

Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo
and Microsoft.SqlServer.SqlEnum
Server sqlServerInstance =
new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(
new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=Master;Integrated Security=True")));
// your connection string I place mine for illustration..
Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");
loginObj.DefaultDatabase = "Master";
loginObj.LoginType = LoginType.WindowsUser;
loginObj.Enable();
loginObj.Create("password");
//set the password
//there're many properties to do some tasks related to Login object...
If you used LoginType.WindowsUser, be sure to provide valid windows username and if you aren’t on Domain use the machine name instead.

If you need to create SQL login use LoginType.SqlLogin…

You can explore Login class more on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx

 

 

 

How to restrict some users to update some tables under some criteria?

Q. How to restrict some users to update some tables under some criteria?

A. If you administer SQL server database, you know that you can grant\deny user updating some tables, but what if you wish to grant\deny user updating some tables when just column equal some value, scenario like that

Table Course (ID, CourseName)

Table Student (ID, Name, CourseID, ….)

Sure, the most common example we give when we teach someone the introduction of databases especially relationships, so if you don’t know it; in brief Student has right to have some courses and the relationship is one-to-many; course-took by- many students.

Going on our scenario, data entry1 responsible for insert\update\delete all students take C# course, data entry2 responsible for insert\update\delete all students take Java course

Clear, isn’t it?

What you should do is to create 2 users dataentry1, and dataentry2 and to create 2 database roles CSharpController, and JavaController.

To create user using SQL Server Management Studio [URL]

To create Database Role using SQL Server Management Studio [URL]

Assign dataentry1 to CSharpController role, and dataentry2 to JavaController, you can assign more users to proper role as you see.

Next step; when user does insert\update\delete operation on Student table, users belong to CSharpController have right if and only if CourseID = 1, and the same for JavaController if and only if CourseID = 2

So, let’s write some code or script; yes, trigger to do those validations

create trigger ManageDML_Student on student for insert, delete, update AS –here to get column value and assign it variable to check its value later IF IS_MEMBER (‘CSharpController’) = 1 && –value == 1–   commit transaction else rollback transaction

Thanks

Acknowledge
I’d like to thank Mohamed Abd ALLAH Hewedy, he helped me through this problem

How to use dataset and adapters with stored procedures?

Q: How to use dataset and adapters with stored procedures?

A: I’ve asked this question two times today, and for that I thought to do a post and send the link better to answer everyone separately..

Right now, I’ve be_settings table, its schema (SettingName nvarchar(50) and SettingValue nvarchar(MAX)) what I need is to create stored procedure to get SettingName and SettingValue when the SettingValue = some value

  

then let’s go to Visual Studio and create windows application and drop DataGridView on the form; press on the smart arow located on its top right, new dialog appears, choose to add new data source->Add project data source->Database

next->next->

then finish…

Your form should look like

in Load data onClick event handler, write this code

Just, Ctr + F5 and lets test our work

Hope, I answered your question ",)

Tip: if you using Windows Vista you need to run Visual Studio and SQL Server as administrator….

How to delete all data from controls in opened forms using C# ???

I will separate this problem into two tasks :

I- Delete all data from one form ( main form )

II- Reaching to other open forms and delete their data .

but before starting , first create a form containsome combo box ,text box , labels and one button

I- Delete all data from one form ( main form ) :

insert this code in the button you created ( clear button )

private void Clear_Click(object sender, EventArgs e) { Control.ControlCollection cc; cc = this.Controls; // to get all controls of your form foreach (Control ctrl in cc) { string type = ctrl.GetType().ToString(); switch (type) { case “System.Windows.Forms.ComboBox”: ComboBox cb = ctrl as ComboBox; cb.SelectedIndex = -1; break; case “System.Windows.Forms.TextBox”: TextBox tb = ctrl as TextBox; tb.Clear(); break; case “System.Windows.Forms.Label”: Label L = ctrl as Label; L.Text = “”; break; } } }

II- Reaching other open forms and delete their data :

its the easy part , just replace one line code and add a for loop .

replace this line :

cc = this.Controls;

with this line :

cc = Application.OpenForms[i].Controls;

* Application.openforms[i] to reach to all open forms

private void Clear_Click(object sender, EventArgs e) { for (int i = 0; i Control.ControlCollection cc; cc = Application.OpenForms[i].Controls; //cc = this.Controls; // previous code } } }

How to make your controls moveable?

Q: How to make your controls moveable?

A: Some people need to add style to their controls to be moveable that’s users can drag and drop them anywhere on the form.So, when user presses on a control and moves the mouse; control location should equal mouse axis.

Some variables used in our code.

/// <summary> /// Indicates whether control is pressed by mouse or not /// </summary> bool IsDraged = false; /// <summary> /// Holds the X-coordinate of the control /// </summary> int Control_X = 0; /// <summary> /// Holds the Y-coordinate of the control /// </summary> int Control_Y = 0;

This is the mouse down event handler, we just checked the left button is pressed then set boolean variable IsDraged to true and assigned control location to mouse location.

private void Control_MouseDown(object sender, MouseEventArgs e) { if (e.Button == MouseButtons.Left) { IsDraged = true; Control_X = e.X; Control_Y = e.Y; } }

Just when mouse left button released we set IsDraged to false.

private void Control_MouseUp(object sender, MouseEventArgs e) { if (e.Button == MouseButtons.Left) { IsDraged = false; } }

Here’s when mouse moves while mouse left button not released yet, the computation used helps controls to move smoothly with the mouse movement.

private void Control_MouseMove(object sender, MouseEventArgs e) { if (e.Button == MouseButtons.Left) { Control active = (Control)sender; if (IsDraged) { active.Left += e.X – Control_X / 12; active.Top += e.Y – Control_Y / 12; Control_X = e.X; Control_Y = e.Y; } } }

How to save image in SQL Server database?

Q: How to save image in SQL Server database?

A: It’s the most common question asked in technical forums, the answer is so simply is to convert your image to binary and save the equivalent binary data to the database.

I am expecting you know how to Insert\get data to\from SQL Server.

To convert image to binary you need to write this piece of code

   1: System.IO.FileStream fs = new System.IO.FileStream(@"ImagePath", System.IO.FileMode.Open);

   2:             byte[] imageAsBytes = new byte[fs.Length];

   3:             fs.Read(imageAsBytes, 0, imageAsBytes.Length);

   4:             fs.Close();

You need a table with column of binary\image datatype to be able to insert the equivalent binary data of image.

And what about retrieving binary data to be converted to image

You just need to initiate new Image from MemoryStream object which takes array of bytes as an argument -Array of bytes comes from SQL Server-

1: Image img = Image.FromStream(new System.IO.MemoryStream(imageAsBytes));

How to bind ComboBox to an array of object?

Q: How to bind ComboBox to an array of object?

A:

Assume we have class like Student class

   1: public class Student

   2:     {

   3:         int id;

   4:  

   5:         public int ID

   6:         {

   7:             get { return id; }

   8:             set { id = value; }

   9:         }

  10:         string firstName;

  11:  

  12:         public string FirstName

  13:         {

  14:             get { return firstName; }

  15:             set { firstName = value; }

  16:         }

  17:         string lastName;

  18:  

  19:         public string LastName

  20:         {

  21:             get { return lastName; }

  22:             set { lastName = value; }

  23:         }

  24:  

  25:         public string FullName

  26:         {

  27:             get { return firstName + " " + lastName; }

  28:         }

  29:  

  30:         public Student(int id, string firstName, string lastName)

  31:         {

  32:             this.id = id;

  33:             this.firstName = firstName;

  34:             this.lastName = lastName;

  35:         }

  36:     }

what we need is to bind an array of Student to ComboBox and let the ComboBox shows the student FullName and bind their IDs to be used later.

So, what we need is to use some ComboBox properties like DisplayMember and ValueMember

   1: Student[] students = new Student[3];

   2:  

   3:             students[0] = new Student(1, "Ramy", "Mahrous");

   4:             students[1] = new Student(2, "FCI", "Helwan");

   5:             students[2] = new Student(3, "X", "Y");

   6:  

   7:             comboBox1.Items.AddRange(students);

   8:             

   9:             comboBox1.DataSource = students;

  10:             comboBox1.ValueMember = "ID";

  11:             comboBox1.DisplayMember = "FullName";

And now, ComboBox shows the FullName without overriding ToString method which is not the solution if we need to bind some properties.